Skip and Take: An efficient approach to OFFSET LIMIT in EF 4.1?

Question

The following code:

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

Will generate the following 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 */

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

NB The Skip and Take have not been translated to SQL resulting in ALL posts from the blog being loaded from the database, instead of just the 5 we require.

This seems dangerously, horribly inefficient. Unbelievably so, what gives?

Answer 1

The reason it's happening is the call to First, which is causing the Blog object to be materialized. Any further traversal requires more queries.

Try db.Blogs.Take(1).SelectMany(b => b.Posts).Skip(10).Take(5).ToList(); instead to do it in one query. You probably want to add some sort of ordering of blogs before the .Take(1), to ensure a deterministic result.

Edit You actually have to use OrderBy before Skip (otherwise LINQ to Entities will throw an exception), which makes it something like:

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