How to limit number of related data with Include

8
votes
class Cat
{
   public int CatID;
   public string Name;
   public ICollection<Post> Posts;
}
class Post
{
   public int PostID;
   public string Name

   public int CatID;
   public virtual Cat Parent;
}

And I want to load all the Cat(egories) with their Posts so:

var cats = context.Cat.Include(c => c.Posts);

Now I want to limit the number of Posts that are returned, can someone show me how to do that?

I'm using EntityFramework 4.3.1

2
entity-frameworkentity-framework-4includeef-code-first

2 Answers

21
votes

It is not possible with eager loading (Include) - eager loading returns always all related data. You must use projections to anonymous or new type (you cannot use your existing mapped entities):

var cats = context.Cat.Select(c => new 
{ 
    Category = c, 
    Posts = c.Posts.OrderBy(p => p.Name).Take(10) 
});
1
votes

You cannot use projections with Include() method but note that in the query below you can limit the number of Categories returned using Name field of Posts.

using (var context = new YourContext())
{
    var categories = from c in context.Categories.Include("Posts")
                    where c.Posts.Any((p)=>p.Name == "Linq")
                    select c;
}

Also you can do something like this:

context.Categories
       .Select(c => new { 
                         Category = c, 
                         Posts = c.Posts.Where(p => p.Name == "Linq") 
       }).AsEnumerable()
       .Select(cp => cp.Category);

Hope it helps.