Paging with Entity Framework 7 and SQL Server 2008

Question

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?

asp.net-mvc

sql-server-2008

paging

entity-framework-core

asked on Stack Overflow May 1, 2015 by

Konstantin • edited May 2, 2015 by

marc_s

7 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

answered on Stack Overflow May 30, 2015 by

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

answered on Stack Overflow Dec 8, 2015 by

Joe Audette

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

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

answered on Stack Overflow Apr 29, 2016 by

Simon Ordo

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.

answered on Stack Overflow Jul 30, 2018 by

Salman Taj • edited Jul 30, 2018 by

alexander.polomodov

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

answered on Stack Overflow Feb 3, 2021 by

Yegor Androsov

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

answered on Stack Overflow Apr 19, 2018 by

Belen Martin

Here, just set UseRowNumberForPaging() in ConfigureServices

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

answered on Stack Overflow Nov 13, 2018 by

Ali Besharati • edited Nov 13, 2018 by

Unheilig

User contributions licensed under CC BY-SA 3.0