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.