CODEWITHSUNDEEP
c#asp.netsql-server-2008asp.net-coreentity-framework-core
Alireza
Alireza

Reputation: 505

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

Here's my code:

 var result = dbContext.Specialty.OrderByDescending(u => u.IdS)
            .Skip(20)
            .Take(10)
            .AsEnumerable();

Error:

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

Upvotes: 13

Views: 21426

Answers (5)

iulo
iulo

Reputation: 151

Just detailing @jawid-hassim answer:

Using .NET 6 + EF Core 6 + SQL Server 2008, you can install the package bellow (wich has a GitHub repository, so the code can be checked for safety).

https://www.nuget.org/packages/EntityFrameworkCore.UseRowNumberForPaging/

After installing the package, in Program.cs add:

using EntityFrameworkCore.UseRowNumberForPaging;

Then replace your DbContext service configuration:

builder.Services.AddDbContext<YourDbContext>(options => options.UseSqlServer(connectionString));

For:

builder.Services.AddDbContext<YourDbContext>(options => options.UseSqlServer(connectionString, o => o.UseRowNumberForPaging()));

Upvotes: 5

Jawid Hassim
Jawid Hassim

Reputation: 397

For anyone using .Net 6 I found this package EntityFrameworkCore.UseRowNumberForPaging 0.3.0: https://www.nuget.org/packages/EntityFrameworkCore.UseRowNumberForPaging/

Base on the github issue: https://github.com/dotnet/efcore/issues/16400

Thank you Rwing, whoever you are, you're a hero!

Upvotes: 12

Yehor Androsov
Yehor Androsov

Reputation: 6162

UseRowNumberForPaging was removed in EF Core 3.x, method is marked as obsolete. However, you can use EfCore3.SqlServer2008Query package instead. There are 2 packages available in Nuget, one for >= .NET 5.0, other one is for >= .NET Core 3.1

enter image description here

Usage:

 services.AddDbContext<MyDbContext>(o => 
       o.UseSqlServer(Configuration.GetConnectionString("Default"))
        .ReplaceService<IQueryTranslationPostprocessorFactory, SqlServer2008QueryTranslationPostprocessorFactory>());

Upvotes: 13

Elliott
Elliott

Reputation: 2729

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()));
    }

Upvotes: 21

Alireza
Alireza

Reputation: 505

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.

Upvotes: 1

Related Questions