CODEWITHSUNDEEP
sqllinqnested
Laco
Laco

Reputation: 53

LINQ query take(count) from collection of child elements

How can I make LINQ query (I am using Entity Framework) that returns top n elements in a child collection?

Here are example classes

public class A {
    public int ID 
    public ICollection<B> bData
}

public class B {
    public int ID 
    public string Name
}

This is kind of query I was thinking;

db.A.Where(a => a.ID == query_id).Include(a => a.bData.Take(count)).ToList();

Normally this doesn't work, how could I accomplish this?

Upvotes: 1

Views: 724

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205629

The Include method is intended for eager loading the related entity data. It's all or nothing and cannot be used for filtering, sorting, grouping etc.

So what are you asking for can be accomplished with projection (select). It would be simpler if you project to anonymous or custom type (a.k.a. DTO object), because EF does not allow projecting to entity type. But it's still doable by using anonymous type projection in LINQ to Entities query and doing second projection in LINQ to Objects to attach the filtered child collection to its parent entity like this:

var result = db.A
    .Where(a => a.ID == query_id)
    .Select(a => new { a, bData = a.bData.Take(count).ToList() })
    .AsEnumerable() // Switch to LINQ to Object context
    .Select(x =>
    {
        x.a.bData = x.bData;
        return x.a;
    })
   .ToList();

Please note that in order the above to work, bData member should not be virtual or db.Configuration.LazyLoadingEnabled should be false (i.e. lazy loading should be off, otherwise the bData collections will be fully reloaded when first accessed).

Upvotes: 1

Related Questions