Paging in SQL Server earlier than 2012

Beware if you want to use a SQL Server version earlier than 2012 while doing paging.

Problem

At least two features of Entity Framework Core depend on SQL Server 2012:

• Using sequences to generate primary keys

• Using OFFSET for pagination

If we are not using SQL Server 2012 or higher, of course, we cannot use these features. The first one is not a problem since we can use IDENTITY columns or manually assigned identifiers, but the second is the default strategy Entity Framework uses for pagination. Type the following LINQ query:

var pagedBlogs = ctx
  .Blogs
  .Skip(4)
  .Take(5)
  .OrderBy(b => b.CreationDate)
  .ToList();

It will produce SQL similar to this:

SELECT [b].[BlogId], [b].[Name], [b].[CreationDate], [b].[Url]
FROM [Blog] AS [b]
ORDER BY [b].[CreationDate]
OFFSET @__b_0 ROWS FETCH NEXT @__b_1 ROWS ONLY

How to solve it…

This syntax with OFFSET… ROWS FETCH NEXT… ROWS ONLY is only valid for versions of SQL Server equal to or higher than 2012. Fortunately, we can tell Entity Framework to use a compatibility mode that will work from SQL Server 2005 upwards. We just need to set the UseRowNumberForPaging configuration setting, probably in the OnConfiguring method of our DbContext-derived class:

protected override void OnConfiguring(
DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(_connectionString, opt =>
    {
        //use ROW_NUMBER instead of OFFSET
        opt.UseRowNumberForPaging();
    });
    base.OnConfiguring(optionsBuilder);
}

After this, the same query will be instead:

SELECT [t].[BlogId], [t].[Name], [t].[CreationDate], [t].[Url]
FROM (
    SELECT [b].[BlogId], [b].[Name], [b].[CreationDate], [b].[Url], ROW_NUMBER() OVER(ORDER BY [b].[CreationDate]) AS [__RowNumber__]
    FROM [Blog] AS [b]
) AS [t]
WHERE ([t].[__RowNumber__] > @__b_0) AND ([t].[__RowNumber__] <= (@__b_0 + @__b_1))

Notice the usage of the ROW_NUMBER function and the nested queries.

Note

You can find a discussion of the two paging techniques in this article: http://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx.