Entity Framework 4 - How to cap the number of child elements from another table (connected via a foreign key)

Question

I have two data models Blog and Post. BlogId is a foreign key on the Post table

public class Blog
{
   public int ID { get; set; }

   public string Title { get; set; }

   public virtual ICollection<Post> Posts { get; set; }

  ...  
}


public class Post
{
   public int ID { get; set; }

   public virtual int BlogId { get; set; }

   public string Title { get; set; }

  ...  
}

Now this works fine and my Repository is happy and pulls everything as expected from DB.

My question is - Is there a way to limit the number of Posts that get retrieved. Perhaps some LINQ magic?

Here is what my current method in the repository looks like:

public Business FindBlog(int id)
{
    return this.context.Get<Blog>().SingleOrDefault(x => x.ID == id);
}

score 3 · Accepted Answer · edited May 23 '17 at 12:29

Unfortunatelly EFv4 doesn't offer easy way to limit number of returned record for navigation property.

If you are using EntityObject derived entities you can use something like:

var blog = context.Blogs
                  .Single(b => b.Id == blogId);

var posts = blog.Posts
                .CreateSourceQuery()
                .OrderByDescending(p => p.Date)
                .Take(numberOfRecords)
                .ToList();

If you are using POCOs you must execute separate query for that (in case of proxied POCOs you can convert navigation property to EntityCollection<Post> to get access to CreateSourceQuery):

var blog = context.Blogs
                  .Single(b => b.Id == blogId);

var posts = context.Posts
                   .Where(p => p.BlogId == blogId)
                   .OrderByDescending(p => p.Date)
                   .Take(numberOfPosts)
                   .ToList();

EFv4.1 and DbContext API offers the way to load only limited number of related entities:

var blog = context.Blogs
                  .Single(b => b.Id == blogId);

context.Entry(blog)
       .Collection(b => b.Posts)
       .Query()
       .OrderByDescending(p => p.Date)
       .Take(numberOfPosts)
       .Load();

Edit:

You can do it in single query with projection:

var blog = context.Blogs
                  .Where(b => b.Id == blogId)
                  .Select(b => new 
                      {  
                          Blog = b,
                          Posts = b.Posts
                                   .OrderByDescending(p => Date)
                                   .Take(numberOfRecords)
                      })
                  .SingleOrDefault()

Just be aware that you must access posts from second paremeter of anonymous type.

Thanks Ladislav. I am using POCOs and I did something similar to your suggestion here. But _I would have loved it if I could have done it in one trip to the DB_. — zuniga, Apr 09 '11 at 22:52

score 0 · Answer 2 · answered Apr 09 '11 at 05:05

0

Do you mean:

public List<Post> GetBlogPosts(Blog blog, int numberOfPosts)
{
    return blog.Posts.Take(numberOfPosts);
}

answered Apr 09 '11 at 05:05

JK.

21,477
35
135
214

I thought that would just simply pick the _numberOfPosts_ posts after the result (with all the posts associated with the blog) are fetched from the DB. I was hoping for something that will actually only bring _numberOfPosts_ from the database. – zuniga Apr 09 '11 at 06:11

Entity Framework 4 - How to cap the number of child elements from another table (connected via a foreign key)

2 Answers2

Linked