Entity Framework: Skip/Take functionality

Question

I'm just curious how Skip and Take functions work in Entity Framework (using EF 6.1).

If I do:

db.Events.OrderByDescending(x => x.Date).Take(maxPageSize).ToList();

I get some list (noticed that one event is completely gone).

If I do:

db.Events.OrderByDescending(x => x.Date).Skip(0).Take(maxPageSize).ToList();

I get another list and that gone event in previous query is present here.

Anyone has idea why do I have to Skip() ZERO entities in order to Take() what I'm supposed to take? It makes almost no sense (at least for me)...

P.S. I can't use SQL Server Profiler to check what queries are generated.

Answer 1

The accepted answer is mostly correct in describing how emitted queries different though the details can vary. I have commonly seen the entity framework emit the query as "TOP (@maxPageSize) .... WHERE [ROW_NUMBER] > @skip" which is probably the same difference, but I am not 100% certain if it generates the same query execution plan.

The important difference to clearly note is that this can produce differing results when your "ORDER BY" does not contain a unique column name.

In code that I had written, we omitted the "Skip" when the @skip value was 0. We had one entry appear as the final entry. If you then went to the next page where the @skip value was applied, the same entry appeared as the first item on that page. The "tied" item that should have been in at least one of those positions never appeared. Here is the SQL that each emitted:

No skip (generated on page 1):

SELECT TOP ({take number}) [Extent1].[Table1ID] AS [Table1ID], {snip a lot of other columns}
        FROM  [dbo].[Table1] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Table2] AS [Extent2] ON [Extent1].[Table2ID] = [Extent2].[Table2ID]
        WHERE ({my where clause conditions})
        ORDER BY [Extent2].[MySortColumn] ASC

Skip:

SELECT TOP ({take number}) [Filter1].[Table1ID], {snip a lot of other columns}
        FROM ( SELECT [Extent1].[Table1ID] AS [Table1ID], {snip a lot of other columns}, row_number() OVER (ORDER BY [Extent2].[MySortColumn] ASC) AS [row_number]
            FROM  [dbo].[Table1] AS [Extent1]
            LEFT OUTER JOIN [dbo].[Table2] AS [Extent2] ON [Extent1].[Table2ID] = [Extent2].[Table2ID]
            WHERE ({my where clause conditions})
        )  AS [Filter1]
        WHERE [Filter1].[row_number] > {skip number}
        ORDER BY [Filter1].[MySortColumn] ASC

The important take away is to go all the way back to "SQL 101" and remember that without "order by", order is not guaranteed. This difference in emitting causing an item to be duplicated on multiple pages of a grid while another item never shows up at all has made me see that this is even more paramount in Entity Framework where the exact SQL produced is not as directly in your control and can differ in future EF versions unexpectedly.

You can currently avoid this by always including Skip(0) which will emit the second query but with {skip number} simply being zero. This appears to, in my tests, always emit the same ordering for tiebreakers using the default rules of T-SQL. I believe it is not best practice to assume that this will necessarily work in future versions of Entity Framework as such though. I would suggest that instead, you consider exploring a tie-breaking strategy of your own. In my case, it should be possible to break the tie on "Table1ID" as that represents a unique identity primary key column. The attached article gives suggestions for more complicated situations though.