Paging with Entity Framework 7 and SQL Server 2008

Question

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?

Please see issue [#1326](https://github.com/aspnet/EntityFramework/issues/1326) and add any comments you may have. — bricelam, May 04 '15 at 15:07

score 43 · Answer 1 · answered May 30 '15 at 17:58

43

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

answered May 30 '15 at 17:58

mesut

2,099
3
23
35

Thanks. Same problem resolved for when my azure database taken at local database. – Rajaram Shelar Jun 09 '15 at 08:59
1

This saved me so much time and this deserves so much more attention. In my case, my development environment was SQL SERVER 2012 and my QA environment is 2008 R2. – Khan Oct 29 '15 at 19:11
If it's changed to 2008, will it still be working with SQL Server 2012? – Yusril Maulidan Raji May 30 '17 at 12:07
Confirmed. It works also for SQL Server 2012. Now, I believe this answer is from heaven ! – Yusril Maulidan Raji May 30 '17 at 15:18
It's 2019, and this fix still works perfectly. Now I wish someone would just update our DB from 2008... Come on DBAs, you can do it. :) – SammyRNYCreal Apr 10 '19 at 13:05

score 15 · Accepted Answer · answered Dec 08 '15 at 20:39

15

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

answered Dec 08 '15 at 20:39

Joe Audette

35,330
11
106
99

3

I did this, and I'm still encountering the said error. – Mickael Caruso Dec 21 '15 at 19:27
Not working in RC 1. Linq "Skip" still tries to generate OFFSET/FETCH NEXT syntax. – Simon Ordo Apr 29 '16 at 19:15

score 4 · Answer 3 · answered Apr 29 '16 at 19:21

4

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

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

answered Apr 29 '16 at 19:21

Simon Ordo

1,517
2
14
23

5

Very useful link. It helped me to solve my problem using: options.UseSqlServer(connection, b => b.UseRowNumberForPaging()); – marianc Aug 12 '16 at 05:41
Very helpful! `useRowNumberForPaging()` solved my issue! – wtf512 Apr 01 '17 at 06:41

score 3 · Answer 4 · answered Feb 03 '21 at 11:40

This feature was removed in EF Core 3.x, UseRowNumberForPaging 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 3.1

Usage:

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

score 2 · Answer 5 · edited Jul 30 '18 at 15:03

2

MyDbConnectionString is Connection string from any source

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(_config["MyDbConnectionString"], 
                options=>
                {
                    options.UseRowNumberForPaging();
                });
}

UseRowNumberForPaging() solved issue in any case except for edmx file scenario.

edited Jul 30 '18 at 15:03

alexander.polomodov

5,396
14
39
46

answered Jul 30 '18 at 14:40

Salman Taj

46
3

Because SQL Server 2008 does not support FETCH and FETCH OFFSET requires orderby() skip() and take() – Salman Taj Jul 30 '18 at 14:42

score 1 · Answer 6 · answered Apr 19 '18 at 07:17

You need to use something like this :

var MinPageRank = (pageIndex - 1) * pageSize + 1;
var MaxPageRank = (pageIndex * pageSize);

var person = _context.Person.FromSql($"SELECT * FROM (SELECT [RANK] = ROW_NUMBER() OVER (ORDER BY Surname),* FROM Person) A WHERE A.[RANK] BETWEEN {MinPageRank} AND {MaxPageRank}").ToList();

IQueryable<Person> PersonIQ = from s in person.AsQueryable() select s;
Person = await PaginatedList<Person>.CreateAsync(PersonIQ .AsNoTracking(), pageIndex ?? 1, pageSize, sourceFull);

Why are you doing pageSize+ 1 ? – Enrico Jun 19 '18 at 09:20 — Enrico, Jun 19 '18 at 09:20

score 0 · Answer 7 · edited Nov 13 '18 at 09:00

0

Here, just set UseRowNumberForPaging() in ConfigureServices

services.AddDbContext<CallcContext>(options => 
options.UseSqlServer(Configuration.GetConnectionString("Connectionstring"),opt=> { opt.UseRowNumberForPaging(); }));

edited Nov 13 '18 at 09:00

Unheilig

16,196
193
68
98

answered Nov 13 '18 at 08:43

Ali Besharati

918
12
25

Paging with Entity Framework 7 and SQL Server 2008

7 Answers7

Linked