Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Paging with Entity Framework 7 and SQL Server 2008

I'm trying to use paging (that is .Skip(...).Take(...) in Entity Framework 7. It works OK with Microsoft SQL Server 2012 and 2014, but fails with the following error on SQL Server 2008:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement.

I've figured out that it is a breaking change in EF version 6.1.2 (http://erikej.blogspot.com/2014/12/a-breaking-change-in-entity-framework.html). But the fix is to modify EDMX file setting ProviderManifestToken attribute to "2008".

The problem is that EF7 currently only supports code-first scenario, thus there is no any EDMX out there. The question is: how to configure ASP.NET 5 website with Entity Framework 7 to use fallback pagination approach for SQL Server older than 2012?

like image 354
Konstantin Avatar asked May 01 '15 21:05

Konstantin


People also ask

Is EF core faster than EF6?

Entity Framework (EF) Core, Microsoft's object-to-database mapper library for . NET Framework, brings performance improvements for data updates in version 7, Microsoft claims. The performance of SaveChanges method in EF7 is up to 74% faster than in EF6, in some scenarios.

How do I Paginate in C#?

The C# pagination logic is contained in a single Pager class that takes the following constructor arguments: totalItems (required) - the total number of items to be paged. currentPage (optional) - the current active page, defaults to the first page. pageSize (optional) - the number of items per page, defaults to 10.


3 Answers

If you use Edmx file, you must open the edmx file using XML Editor and change

ProviderManifestToken="2012" ==> ProviderManifestToken="2008"

in line 7.

Please take a look at this blog post for more information: http://erikej.blogspot.com.tr/2014/12/a-breaking-change-in-entity-framework.html

like image 117
mesut Avatar answered Oct 21 '22 06:10

mesut


I encountered this problem myself using EF 7 and sql server 2008. Fortunately in the latest rc1 version of EF 7 you can solve this by using .UseRowNumberForPaging() as shown in this example:

services.AddEntityFramework()
  .AddSqlServer()
  .AddDbContext<YourDbContext>(options =>
     options.UseSqlServer(configuration["Data:DefaultConnection:ConnectionString"])
                    // this is needed unless you are on mssql 2012 or higher
                    .UseRowNumberForPaging()
                );
like image 34
Joe Audette Avatar answered Oct 21 '22 08:10

Joe Audette


It's broken in RC 1. Gotta wait to get RC 2.

https://github.com/aspnet/EntityFramework/issues/4616

like image 4
Simon Ordo Avatar answered Oct 21 '22 08:10

Simon Ordo