When working with a large number of rows in a table, sometimes you need to retrieve just a part of them. It could be the first N rows (for example, from 1 till 100) or any of the subsequent blocks with N rows (maybe 101…200, or 701…800). The last technique is also called pagination.
LINQ offers four methods to restrict the output:
- Take() and Skip() – with a concrete number of rows;
- TakeWhile() and SkipWhile() – with a lambda expression.
You can find the descriptions of these methods in MSDN article Enumerable Methods.
Let’s take this simple query.
1 2 |
var query181 = ctx.Clients .Take(100); |
It’s not a good idea to take first rows without any ordering because SQL Server does not guarantee the order of rows, especially when the table does not have a clustered index. But it works and Entity Framework generates the following T-SQL code:
1 2 3 4 5 6 |
SELECT TOP (100) [c].[Id] AS [Id], [c].[Name] AS [Name], [c].[CountryCode] AS [CountryCode], [c].[Timestamp] AS [Timestamp] FROM [dbo].[Clients] AS [c] |
Good! The code is correct, it uses a well-known SELECT TOP statement.
Now I want to get next 100 rows. But Entity Framework requires an explicit ordering now. OK, I place an explicit OrderBy() method.
1 2 3 4 |
var query182 = ctx.Clients .OrderBy(c => c.Id) .Skip(100) .Take(100); |
You’ll be surprised that Entity Framework could generate different T-SQL code here. It depends on the version of SQL Server you’re working with. For SQL Server 2012+ it generates code with OFFSET/FETCH.
1 2 3 4 5 6 7 8 |
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[CountryCode] AS [CountryCode], [Extent1].[Timestamp] AS [Timestamp] FROM [dbo].[Clients] AS [Extent1] ORDER BY [Extent1].[Id] ASC OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY |
For previous versions (I’ve tested on SQL Server 2008R2) it uses ROW_NUMBER() function.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT TOP (100) [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[CountryCode] AS [CountryCode], [Extent1].[Timestamp] AS [Timestamp] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[CountryCode] AS [CountryCode], [Extent1].[Timestamp] AS [Timestamp], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number] FROM [dbo].[Clients] AS [Extent1] ) AS [Extent1] WHERE [Extent1].[row_number] > 100 ORDER BY [Extent1].[Id] ASC |
* I’ve indented the inner subquery to improve the readability.
Great! Entity Framework does the best it could do here 🙂