实体框架:跳过/获取功能 [英] Entity Framework: Skip/Take functionality

问题描述

我只是好奇 Skip 和 Take 函数在 Entity Framework 中是如何工作的(使用 EF 6.1).

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

如果我这样做:

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

我得到了一些列表(注意到一个事件完全消失了).

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

如果我这样做:

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.

任何人都知道为什么我必须 Skip() ZERO 实体才能 Take() 我应该采取的?这几乎没有意义(至少对我来说)......

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

附:我无法使用 SQL Server Profiler 检查生成了哪些查询.

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

推荐答案

尽管细节可能有所不同，但在描述发出的查询如何不同方面，公认的答案大多是正确的.我经常看到实体框架将查询发出为TOP (@maxPageSize) .... WHERE [ROW_NUMBER] > @skip"，这可能是相同的差异，但我不能 100% 确定它是否生成相同的查询执行计划.

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.

需要明确注意的重要区别是，当您的ORDER BY"不包含唯一的列名时，这会产生不同的结果.

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

在我编写的代码中，当@skip 值为0 时，我们省略了Skip".我们有一个条目作为最终条目出现.如果您随后转到应用了@skip 值的下一页，则相同的条目将作为该页面上的第一项出现.应该至少处于其中一个位置的绑定"项目从未出现.这是每个发出的 SQL:

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:

没有跳过(在第 1 页生成):

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

跳过:

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

重要的一点是回到SQL 101"并记住 没有order by"，订单不保证.这种发射差异导致一个项目在网格的多个页面上重复，而另一个项目根本不显示，这让我看到这在实体框架中更为重要，因为生成的确切 SQL 并不直接在您的控制中，并且在未来的 EF 版本中可能会出乎意料地不同.

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.

您目前可以通过始终包含将发出第二个查询但 {skip number} 简单为零的 Skip(0) 来避免这种情况.在我的测试中，这似乎总是使用 T-SQL 的默认规则为决胜局发出相同的顺序.我认为假设这在实体框架的未来版本中必然会起作用并不是最佳实践.相反，我建议您考虑探索 平局策略你自己.在我的情况下，应该可以打破Table1ID"的关系，因为它代表一个唯一的身份主键列.不过，随附的文章针对更复杂的情况给出了建议.

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.

这篇关于实体框架:跳过/获取功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！