实体框架：跳过/接受功能 [英] Entity Framework: Skip/Take functionality

问题描述

我很好奇（在EF 6.1中）跳过和获取功能在Entity Framework中的工作方式。

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（） 零个实体才能获得 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)...

P.S。我无法使用SQL Server Profiler来检查生成了哪些查询。

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

推荐答案

公认的答案在描述如何发出时基本上是正确的尽管细节可能有所不同，但查询有所不同。我通常看到实体框架以 TOP（@maxPageSize）.... WHERE [ROW_NUMBER]> @skip发出查询，这可能是相同的区别，但是我不确定它会生成相同的查询执行吗？

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值的下一页，则该条目将作为该页面的第一项出现。应该至少处于那些位置之一的捆绑物品从未出现过。以下是每个发出的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（0）来避免这种情况，后者会发出第二个查询，但带有{skip number}只是零。在我的测试中，这似乎总是使用T-SQL的默认规则为平局决胜局发出相同的顺序。我认为，并不是最好的做法是假定这在将来的Entity Framework这样的版本中一定会起作用。我建议您改用打破平局策略你自己。就我而言，应该有可能打破 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屋！