Entity Framework Pitfalls: Skip/Take Position Matters
In LINQ, you normally do paging through the Skip and Take operators. These are the LINQ counterparts to whatever the relational database uses for pagination – ROW_NUMBER(), LIMIT…OFFSET, TOP, ROWNUM, etc.
There are some gotchas to it:
- When you do paging, Entity Framework demands that you add ordering to your query. This makes sense as without an explicit ORDER BY clause, search results may come in an unexpected order;
Consider these two LINQ queries:
ctx
.MyEntities
.Where(x => x.Name == "")
.OrderBy(x => x.Id)
.Skip(10)
.Take(5)
.ToList();
And:
ctx
.MyEntities
.OrderBy(x => x.Id)
.Skip(10)
.Take(5)
.Where(x => x.Name == "")
.ToList();
The first one will produce this SQL:
SELECT TOP (5)
[Filter1].[Id] AS [Id],
[Filter1].[Name] AS [Name],
[Filter1].[Date] AS [Date]
FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].
[Date] AS [Date], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number
]
FROM [dbo].[MyEntities] AS [Extent1]
WHERE N'' = [Extent1].[Name]
) AS [Filter1]
WHERE [Filter1].[row_number] > 10
ORDER BY [Filter1].[Id] ASC
Whereas the second will produce this one:
SELECT
[Limit1].[Id] AS [Id],
[Limit1].[Name] AS [Name],
[Limit1].[Date] AS [Date]
FROM ( SELECT TOP (5) [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Date] AS [Date]
FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Date] AS [Date], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]
FROM [dbo].[MyEntities] AS [Extent1]
) AS [Extent1]
WHERE [Extent1].[row_number] > 10
ORDER BY [Extent1].[Id] ASC
) AS [Limit1]
WHERE N'' = [Limit1].[Name]
ORDER BY [Limit1].[Id] ASC
Basically, the TOP(5) expression is placed in the wrong place. So, in LINQ, it’s not just the presence of a Skip and Take operator, it’s where you place it that matters!