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...