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?
Accepted Answer
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
Popular Answer
You can try to get your first blog and use the blog id to filter posts like this:
Blog blog = db.Blogs.First();
blog.posts = Posts.Where(r=>r.blogID=blog.id).Skip(10).Take(5).ToList();
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
