Efcore: EF7 (RC1) - Cannot Skip() and Take() in SQL Server 2008
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();
john182
All 23 comments
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?
natemcmaster
on 22 Feb 2016
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
john182
on 22 Feb 2016
.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?
rowanmiller
on 22 Feb 2016
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 :
``` C#
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 :
``` C#
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"
}
}
}
@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
on 23 Feb 2016
@john182 just tried to grab the project, but it says it is no longer available.
rowanmiller
on 26 Feb 2016
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:
``` c#
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:
``` SQL
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
good night. So I have to upgrade to RC2 , however according to this page :
https://github.com/aspnet/home/wiki/roadmap
is not available
john182
on 27 Feb 2016
@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.
rowanmiller
on 27 Feb 2016
I understand , there is a possibility by the end of next month leaving the RC2 version since it was set for February
john182
on 27 Feb 2016
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.
rowanmiller
on 27 Feb 2016
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 .
john182
on 27 Feb 2016
Definitely not February... possibly March (we'll be sharing dates when we know when things will stabilize).
rowanmiller
on 27 Feb 2016
good we awaited and I THINK IN A palliative solution , but thank you For the Attention hope MANY good things in this new version .
john182
on 27 Feb 2016
any sort of timeline on RC2? it's really hard building production apps using EF7 with this bug... thanks
giancarloa
on 27 Mar 2016
@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.
rowanmiller
on 4 Apr 2016
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());
tobbylee1
on 24 May 2016
@tobbylee1 This API now uses a nested closure pattern:
C#
options.UseSqlServer(connection, b => b.UseRowNumberForPaging());
ajcvickers
on 24 May 2016
@ajcvickers Thank You! It's working now.
tobbylee1
on 24 May 2016
Hello! Where did you put this code? At the project.json file? thank you
mspace23
on 21 Oct 2016
@mspace23 this goes in OnConfiguring in your context class (or Startup.cs if you are using ASP.NET Core)
rowanmiller
on 21 Oct 2016
thank you very much for your quicl reply! I put it to my Startup.cs since I use asp.net Core! It works great!
mspace23
on 22 Oct 2016
The code goes in your Startup.cs file in the section to add services.
On Oct 21, 2016, at 8:13 AM, Rowan Miller [email protected] 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.
tobbylee1
on 25 Oct 2016
Related issues
HeMinzhang
·
3Comments
ghost
·
3Comments
miguelhrocha
·
3Comments
januszj
·
3Comments
pachecosoftware
·
3Comments
Most helpful comment
@tobbylee1 This API now uses a nested closure pattern:
C# options.UseSqlServer(connection, b => b.UseRowNumberForPaging());