EF7 (RC1) - Cannot Skip() and Take() in SQL Server 2008

[john182]

I am aware that SQL Server 2012 and above has a new OFFSET syntax for pagination, which Entity Framework 7 seems to translate my LINQ to.

This explains why I'm getting the error Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement. because SQL Server 2008 doesn't recognize it

my config:

var conn = Configuration["Data:DefaultConnection:ConnectionString"];
optionsBuilder.UseSqlServer(conn).UseRowNumberForPaging();

[natemcmaster]

Can you share a few more details? Which version of EF7 EF Core are you using? What is the exact LINQ query that produces incorrect SQL? What is the SQL it produces?

[john182]

the error happens in any consultation execultando even a simple query type :

(from p in ctx.Produtcs select p).Skip(0).Take(10)

SELECT [p].[Id], [p].[Name]
FROM [Product] AS [p]
ORDER BY @@ROWCOUNT
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

"EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
    "EntityFramework.Commands": "7.0.0-rc1-final",
    "EntityFramework.Core": "7.0.0-rc1-final",
    "Microsoft.AspNet.Identity.EntityFramework": "3.0.0-rc1-final",


 "dnxcore50": {
      "EntityFramework.Core": "7.0.0-rc1-final",
      "frameworkAssemblies": {

      }

version as.net core 1 rc1

[rowanmiller]

.UseRowNumberForPaging(); should take care of generating SQL 2008 compatible SQL. Could you share the full code listing from Startup.cs, your context, and the application code where the query is failing?

[john182]

my project is the following estrtura :
1 project containing all Model ( Entity Classes, repository and DbContext )

1 project containing all the related part of the business rules and view

DbContext :

namespace PainelNfeNfceDominio.Repositorio.Abstract
{
    public class BancoContext : DbContext
    {
        public BancoContext()
        {
            var builder = new ConfigurationBuilder()
                         .AddJsonFile("config.json");

            Configuration = builder.Build();
        }

        public DbSet<NotaSaida> NotaSaida { get; set; }
        public DbSet<NotaSaidaProd> NotaSaidaProd { get; set; }
        public DbSet<Produtos> Produto { get; set; }
        public DbSet<CadEmpresa> Empresa { get; set; }
        public DbSet<Clientes> Cliente { get; set; }
        public DbSet<Users> Usuario { get; set; }
        public DbSet<PagamentosPedido> Pagamentos { get; set; }

        IConfiguration Configuration { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {

           // optionsBuilder.UseSqlServer(@"Data Source=192.168.0.167\Sistemas;Initial Catalog=AnselmoAutoSkape;User Id=sa;Pwd=#jamsoftsistemas1310;MultipleActiveResultSets=True").UseRowNumberForPaging();
           optionsBuilder.UseSqlServer(@"Data Source=(local)\DEVELOPER;Initial Catalog=Varejo;User Id=sa;Pwd=p@ssw0rd;MultipleActiveResultSets=True");
            //var conn = Configuration["Data:DefaultConnection:ConnectionString"];
            //optionsBuilder.UseSqlServer(conn).UseRowNumberForPaging();
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }
    }
}

Startup :

    public class Startup
    {

        public Startup(IHostingEnvironment env)
        {
            // Set up configuration sources.
            var builder = new ConfigurationBuilder()
                .AddJsonFile("appsettings.json")
                .AddEnvironmentVariables();

            if (env.IsDevelopment())
            {
                // This will push telemetry data through Application Insights pipeline faster, allowing you to view results immediately.
                builder.AddApplicationInsightsSettings(developerMode: true);
            }
            Configuration = builder.Build();
        }

        public IConfigurationRoot Configuration { get; set; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // Add framework services.
            services.AddApplicationInsightsTelemetry(Configuration);
            services.AddSession();
            services.AddCaching();
            services.AddMvc();
            services.AddLogging();
           // services.AddEntityFramework().AddSqlServer().AddDbContext<BancoContext>();

        }
        public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
        {
            app.Map("/painelnfe", map => Conf(map, env, loggerFactory));
        }
        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Conf(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
        {
            loggerFactory.AddConsole(Configuration.GetSection("Logging"));
            loggerFactory.AddDebug();

            app.UseApplicationInsightsRequestTelemetry();
            app.UseSession();
            app.UseWebSockets();


            if (env.IsDevelopment())
            {
                app.UseBrowserLink();
                app.UseDeveloperExceptionPage();             
                loggerFactory.AddDebug(LogLevel.Verbose);
                app.UseDatabaseErrorPage();

            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
            }
            app.UseStatusCodePagesWithReExecute("/Error/Status/{0}");
            app.UseIISPlatformHandler();

            app.UseApplicationInsightsExceptionTelemetry();

            app.UseStaticFiles();

            app.UseMvc(routes =>
            {
                routes.MapRoute(
                   name: "default",
                   template: "{controller=Usuario}/{action=Login}/{id?}");
            }
}

config.json

{
  "Data": {
    "DefaultConnection": {
      "ConnectionString": "Server=192.168.0.167\\Sistemas;Initial Catalog=Supermercado;User Id=sa;Pwd=senha;MultipleActiveResultSets=True"

    }
  },
  "EntityFramework": {
    "ApplicationDbContext": {
      "ConnectionStringKey": "Data:DefaultConnection:ConnectionString"
    }
  }
}

[rowanmiller]

@john182 I don't see anything obviously wrong in the code listings. Any chance you could share the whole project so that I can run it and see what is happening?

[rowanmiller]

@john182 just tried to grab the project, but it says it is no longer available.

[rowanmiller]

Ok, turns out UseRowNumberForPaging() just doesn't work in RC1, but it has been fixed in our working code base and will work in RC2.

Here is repro code I used:

using Microsoft.Data.Entity;
using System.Linq;

namespace RC1.Demo
{
    public class Program
    {
        static void Main(string[] args)
        {
            using (var context = new BancoContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                var tst = context.Blogs.Skip(0).Take(10).ToList();
            }
        }
    }

    public class BancoContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {

            optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Initial Catalog=Sample;Trusted_Connection=True;")
                .UseRowNumberForPaging();
        }
    }

    public class Blog
    {

        public int BlogId { get; set; }
        public string Url { get; set; }
    }
}

RC1 query is:

SELECT [b].[BlogId], [b].[Url]
FROM [Blog] AS [b]
ORDER BY @@ROWCOUNT
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Query from current code base is:

exec sp_executesql N'SELECT [t].[BlogId], [t].[Url]
FROM (
    SELECT [b].[BlogId], [b].[Url], ROW_NUMBER() OVER(ORDER BY @@RowCount) AS [__RowNumber__]
    FROM [Blog] AS [b]
) AS [t]
WHERE ([t].[__RowNumber__] > @__p_0) AND ([t].[__RowNumber__] <= (@__p_0 + @__p_1))',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10

[john182]

good night. So I have to upgrade to RC2 , however according to this page :
https://github.com/aspnet/home/wiki/roadmap
is not available

[rowanmiller]

@john182 correct, RC2 has not shipped yet. You could use our nightly builds - but things are pretty unstable at the moment due to the transition to .NET CLI... so I wouldn't encourage it.

Unfortunately there isn't really a good workaround for RC1. You would have to do the paging in-memory or use a raw SQL query.

[john182]

I understand , there is a possibility by the end of next month leaving the RC2 version since it was set for February

[rowanmiller]

RC2 is currently listed as TBD in the roadmap while we stabilize after moving to .NET CLI. There isn't a release date at this stage - but it definitely won't be by the end of this month.

[john182]

possibly it would be unlikely to leave until the end of the month I would refer to the month of March , but I atrapanhando me .

[rowanmiller]

Definitely not February... possibly March (we'll be sharing dates when we know when things will stabilize).

[john182]

good we awaited and I THINK IN A palliative solution , but thank you For the Attention hope MANY good things in this new version .

[giancarloa]

any sort of timeline on RC2? it's really hard building production apps using EF7 with this bug... thanks

[rowanmiller]

@giancarloa - no exact dates yet, we're still stabilizing things after moving from DNX to .NET CLI. We'll share dates when we have them.

[tobbylee1]

I'm using "Microsoft.EntityFrameworkCore.SqlServer": "1.0.0-rc2-final" and according to this post, I would just need to add the option to.UseRowNmberForPaging() when creating a new DBcontext. But when I add the option, it's not recognized. Can anyone point me in the right direction? Trying to paginate records on SQL Server 2008 so this seems to be the recommended solution. Below is the line I'm using to Configure the service. Thanks!

        services.AddDbContext<Data.Models.AC_MCLContext>(options =>
               options.UseSqlServer(connection).UseRowNumberForPaging()); 

[ajcvickers]

@tobbylee1 This API now uses a nested closure pattern:

options.UseSqlServer(connection, b => b.UseRowNumberForPaging());

[tobbylee1]

@ajcvickers Thank You! It's working now.

[mspace23]

Hello! Where did you put this code? At the project.json file? thank you

[rowanmiller]

@mspace23 this goes in OnConfiguring in your context class (or Startup.cs if you are using ASP.NET Core)

[mspace23]

thank you very much for your quicl reply! I put it to my Startup.cs since I use asp.net Core! It works great!

[tobbylee1]

The code goes in your Startup.cs file in the section to add services.

On Oct 21, 2016, at 8:13 AM, Rowan Miller notifications@github.com wrote:

@mspace23 this goes in OnConfiguring in your context class (or Startup.cs if you are using ASP.NET Core)

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[yegorandrosov]

getting the same issue with EF Core 3.1.9

or was it completely removed int EF Core 3.x?
https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.x/breaking-changes#userownumberforpaging-has-been-removed

[CodeOutput]

any solition please ... EF.Core 3.1.21
i use UseRowNumberForPaging() not working