Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement "in Entity Framework core"

asp.net asp.net-core c# entity-framework-core sql-server-2008

Question

Here's my EF Core code:

 int page = 1, rowPerPage = 5;
 int count = ctx.Specialty.Count();
 int start = page * rowPerPage;

 var Select = ctx.Specialty.OrderByDescending(u => u.IdS)
            .Skip(start)
            .Take(rowPerPage)
            .AsEnumerable();

Error:

Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement

1
4
3/18/2020 3:20:14 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

There is a compatibility setting (UseRowNumberForPaging) for this which can be configured either in the DbContext itself:

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var coonectionString = "Data Source=localhost\\MSSQLSERVER01;Initial Catalog=AppDb01;Integrated Security=True";
        optionsBuilder.UseSqlServer(coonectionString, builder => builder.UseRowNumberForPaging());
    }

Or as a part of the Startup:

    public void ConfigureServices(IServiceCollection services)
    {
        var coonectionString = "Data Source=localhost\\MSSQLSERVER01;Initial Catalog=AppDb01;Integrated Security=True";
        services.AddDbContext<AppDbContext>(options => options.UseSqlServer(coonectionString, builder => builder.UseRowNumberForPaging()));
    }
14
1/15/2019 2:34:26 PM

Popular Answer

sql server 2008 not support from my query

solution:

public class AppDbContext : DbContext
{

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var coonectionString = "Data Source=localhost\\MSSQLSERVER01;Initial Catalog=AppDb01;Integrated Security=True";
        optionsBuilder.UseSqlServer(coonectionString);
    }
}

Value connection string to the Target server and also inject the settings , The sample code is in the default ASP NET Core project format.

Alireza

Related Questions

Error while executing db.Database.Migrate(): Incorrect syntax near the keyword 'NOT'

I have an entity relationship syntax error. ApplicationDbContext has a few relationship issues that I did not resolve....Basically, please check your own ApplicationDbContext before you migrate.
asp.net-core asp.net-core-mvc c# entity-framework-core
asked by Nicholas

Offset/Fetch based paging (Implementation) in EntityFramework (Using LINQ) for SQL Server 2008

I am using SQL Server 2008 and Entity Framework 6.1.3. I wish to implement pagination of data based on OFFSET/FETCH rather than Take() & Skip()....I searched online with no luck. Some posts suggested migrating to SQL Server 2012. Which is not an option in my case....Can someone suggest how to use OFFSET/FETCH with SQL Server 2008 and EF 6.1.3
entity-framework entity-framework-6 pagination sql-server sql-server-2008
asked by Immortal

Installing Entity Framework Core 2.0 in a .NET Framework Class Library Project Yields Invalid References

This can be fixed by letting MSBuild autogenerate the necessary binding redirects by explicitly setting these two properties inside the ...csproj... file (You can put the ...<ItemGroup>... as a child element below the root ...<Project>... element or add to an exiting ...<ItemGroup>... without a ...Condition=... attribute):...<PropertyGroup> <Auto...
.net-core-2.0 c# entity-framework-core visual-studio-2017
asked by Martin Ullrich

Incorrect syntax near 'MERGE' Entity Framework Core

Don't do the AddRange. Just add a ClientId to the Phone entity (assuming that one phone will only belong to one client). The phones will be then saved when adding a Client....public class Client { public int Id { get; set; } public string Name{ get; set; } public string Lastname{ get; set; } public int DNI { get; set; } public List<Phones> Phones{ ...
asp.net-core c# entity-framework-core
asked by joaoruimartins

Parameterised OFFSET FETCH NEXT query (EF Core) has 10x slower performance

Please add an index that supports the ORDER BY ([dto].[Forename], [dto].[Id])and that covers all of the columns in the SELECT clause. ...CREATE NONCLUSTERED INDEX IX_Associates_Forename_ID ON Associates (Forename, Id) INCLUDE (Surname, AzureId, Email); ...As you can see in the plan, doing pagination without proper index originates scans or key...
azure-sql-database entity-framework-core sql
asked by Alberto Morillo

.NET Core Entity Framework throws Invalid object name when accessed as entity, but works with SQL

I found the solution. You can't put the schema in the table name....//This Does NOT work [Table("Cdef.CellDefinition")] public partial class CellDefinition{} //But this DOES work [Table("CellDefinition",Schema = "Cdef")] public partial class CellDefinition{}
asp.net-mvc entity-framework entity-framework-core
asked by John P

EF Core SQLite in memory exception: SQLite Error 1: 'near "MAX": syntax error'

I am creating SQLite In Memory database for unit testing:... var connection = new SqliteConnection("DataSource=:memory:"); connection.Open(); try { var options = new DbContextOptionsBuilder<BloggingContext>() .UseSqlite(connection) .Options; // Create the schem...
c# ef-core-2.0 ef-core-2.1 entity-framework-core sqlite
asked by Deivydas Voroneckis

Entity Framework Core Insert multiple entities similar to SQL single insert statement

How do you manage to get the same effect as SQL simple...INSERT INTO myTable VALUES (X), (Y), (Z),..... ...and so on?
entity-framework-core performance
asked by Jackal

Entity Framework Core MigrationBuilder "ALTER DATABASE statement not allowed within multi-statement transaction."

You should put each command in a separate .Sql statement inside your migration code....Also, you should ensure to suppress transaction for each statement, like:...migrationBuilder.Sql("ALTER DATABASE ListManagement SET READ_COMMITTED_SNAPSHOT ON", true); ...Sql method signature is outlined ...here.......suppressTransaction - Boolean ...Indicates wh...
.net-core c# entity-framework-core entity-framework-core-migrations
asked by hamiltonjose

EF Core: fetch two SELECT statements in a single call by FromSql()

Here is what I would do in this case...-- A very simple SP CREATE PROCEDURE dbo.Sample AS BEGIN Declare @TotalCount int SELECT @TotalCount = COUNT(*) FROM Some_Table s as TotalRecord ...SELECT TOP 10 @TotalCount as TotalCount, c.Id, s.CreationDate, s.FirstName, s.LastName FROM Some_Table s ORDER BY s.CreationDate...END
ef-core-2.2 entity-framework-core sql-server
asked by John Meek

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow