EF4 querying from parent to grandchildren

Question

I have a model withs Parents, Children and Grandchildren, in a many-to-many relationship. Using this article I created POCO classes that work fine, except for one thing I can't yet figure out.

When I query the Parents or Children directly using LINQ, the SQL reflects the LINQ query (a .Count() executes a COUNT in the database and so on) - fine. The Parent class has a Children property, to access it's children. But (and now for the problem) this doesn't expose an IQueryable interface but an ICollection. So when I access the Children property on a particular parent all the Parent's Children are read. Even worse, when I access the Grandchildren (theParent.Children.SelectMany(child => child.GrandChildren).Count()) then for each and every child a separate request is issued to select all data of it's grandchildren. That's a lot of separate queries!

Changing the type of the Children property from ICollection to IQueryable doesn't solve this. Apart from missing methods I need, like Add() and Remove(), EF just doesn't recognize the navigation property then.

Are there correct ways (as in: low database interaction) of querying through children (and what are they)? Or is this just not possible?

Answer 1

A workaround I found was starting in the middle, at the Children:

var gc = context.Children
  .Where(c => c.Parents.Any(p => p.Id == theParent.Id))
  .SelectMany(c => c.GrandChildren);

int cnt = gc.Count();

At least this gives a sql-query that returns only the number of grandchildren and not all intermediate data.