c# - 跳过并拿: An efficient approach to OFFSET LIMIT in EF 4. 1？

如下代码:

using (var db = new Entities())
{
    db.Blogs.First().Posts.Skip(10).Take(5).ToList();
}

将生成以下SQL:

-- statement #1
SELECT TOP ( 1 ) [c].[Id] AS [Id],
             [c].[Title]          AS [Title],
             [c].[Subtitle]       AS [Subtitle],
             [c].[AllowsComments] AS [AllowsComments],
             [c].[CreatedAt]      AS [CreatedAt]
FROM [dbo].[Blogs] AS [c]

-- statement #2
SELECT [Extent1].[Id] AS [Id],
   [Extent1].[Title]    AS [Title],
   [Extent1].[Text]     AS [Text],
   [Extent1].[PostedAt] AS [PostedAt],
   [Extent1].[BlogId]   AS [BlogId],
   [Extent1].[UserId]   AS [UserId]
FROM [dbo].[Posts] AS [Extent1]
WHERE [Extent1].[BlogId] = 1 /* @EntityKeyValue1 */

(来自http://ayende.com/blog/4351/nhibernate-vs-entity-framework-4-0)

注意:“跳过并接受”未转换为SQL，导致博客中的所有帖子都从数据库中加载，而不仅仅是我们要求的5个。

这似乎很危险，效率极低。令人难以置信的是，那有什么作用？

最佳答案

发生这种情况的原因是对First的调用，这导致Blog对象得以实现。任何进一步遍历都需要更多查询。

尝试使用db.Blogs.Take(1).SelectMany(b => b.Posts).Skip(10).Take(5).ToList();在一个查询中进行操作。您可能想要在.Take(1)之前添加某种类型的博客排序，以确保确定的结果。

编辑
实际上，您必须在跳过之前使用OrderBy(否则LINQ to Entities将引发异常)，这使其类似于:

db.Blogs.OrderBy(b => b.Id).Take(1) // Filter to a single blog (while remaining IQueryable)
    .SelectMany(b => b.Posts) // Select the blog's posts
    .OrderBy(p => p.PublishedDate).Skip(10).Take(5).ToList(); // Filter to the correct page of posts