SQL Server 2008 and EF7 (RC1) - Cannot Skip() and Take()?
asp.net-core entity-framework-core pagination sql-server-2008
Question
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.
In this question, someone answered that all we need to do, as of RC1 is to tack on .UseRowNumberForPaging() at Startup.cs's ConfigureServices().
services.AddEntityFramework()
.AddSqlServer()
.AddDbContext<MyDbContext>(options => {
options.UseSqlServer("someConnectionString").UseRowNumberForPaging();
});
This doesn't seem to work. I am still getting the same error. My guess is EF7 still compiles my LINQ to the new 2012+ SQL syntax. What to do?
Popular Answer
Your question seems to be have been cross-posted and solved at github.
The solution to this problem is, as stated by Arthur Vickers, to go to your project's Startup.cs and add UseRowNumberForPaging() to your db's options builder.
services.AddDbContext<ApplicationDbContext>(
options => {
options.UseSqlServer(
this.config.GetConnectionString("MyDatabaseConnectionString"),
builder => builder.UseRowNumberForPaging()
);
}
);
I tested this with .NET Core 2 and it works fine.