Entity Framework: Querying Child Entities

Question

I'm learning about Entity Framework at the mo, and am having problems!!

Can someone clarify if I am right in thinking that I can't get a parent and a subset of it's children from the db?

For example...

db.Parents
.Include(p => p.Children)
.Where(p => p.Children.Any(c => c.Age >= 5))

This will return all Parents that have a child aged 5+, but if I iterate through the Parents.Children collection, all children will be present (not just those over 5 years old).

Now the query does make sense to me (I've asked to include children and I've got them!), but can imagine that I would like to have the where clause applied to the child collection in some scenarios.

Questions:

1. Is what I have said correct?
2. Is it possible to get the parents and just a subset from the db without make loads of calls to the db?
3. Am I way off the mark? (Wouldn't be the 1st time)!!!!

I have found a few blogs and SO posts that touch on the subject, but nothing that explains it well enough for my little brain.

EDIT

Having read this blog (thanks Daz Lewis).......I still don't get it!!!

In the example given in the blog, I can see how I can achieve it against a single instance of Parent, but I'm struggling to work out how I can do it with a collection.

How could I get an IEnumerable, in which each of the parents has a filtered collection of Children (Age>=5)?

Further clarification:

In answer to DonAndre's comment, I am after a) A list of parents who have a child older than 5 (and include only those children).

Any help appreciated,

Thanks.

Answer 1

The only way to get a collection of parents with a filtered children collection in a single database roundtrip is using a projection. It is not possible to use eager loading (Include) because it doesn't support filtering, Include always loads the whole collection. The explicite loading way shown by @Daz requires one roundtrip per parent entity.

Example:

var result = db.Parents
    .Select(p => new
    {
        Parent = p,
        Children = p.Children.Where(c => c.Age >= 5)
    })
    .ToList();

You can directly work with this collection of anonymous type objects. (You can also project into your own named type instead of an anonymous projection (but not into an entity like Parent).)

EF's context will also populate the Children collection of the Parent automatically if you don't disable change tracking (using AsNoTracking() for example). In this case you can then project the parent out of the anonymous result type (happens in memory, no DB query):

var parents = result.Select(a => a.Parent).ToList();

parents[i].Children will contain your filtered children for each Parent.

---

Edit to your last Edit in the question:

I am after a) A list of parents who have a child older than 5 (and include only those children).

The code above would return all parents and include only the children with Age >= 5, so potentially also parents with an empty children collection if there are only children with Age < 5. You can filter these out using an additional Where clause for the parents to get only the parents which have at least one (Any) child with Age >= 5:

var result = db.Parents
    .Where(p => p.Children.Any(c => c.Age >= 5))
    .Select(p => new
    {
        Parent = p,
        Children = p.Children.Where(c => c.Age >= 5)
    })
    .ToList();