EF4 request from parents to grandchildren

Question

I have a model with parents, children and grandchildren in many ways. Using in this article I created POCO classes that work just fine, except for one thing that I still cannot understand.

When I directly contact parents or children using LINQ, SQL reflects the LINQ query (a .Count()executes COUNT in the database, etc.) - fine. The Parent class has the Children property to access children. But (and now for the problem) this does not show the interface IQueryable, but << 22>. Therefore, when I access the Children resource for a specific parent, all parent children are read. Worse, when I refer to the grandchildren ( theParent.Children.SelectMany(child => child.GrandChildren).Count()), then for each child is given a separate query to select all the data of his grandchildren. These are many separate requests!

Changing the type of the Children property from ICollection to IQueryable does not solve this. Besides the missing methods that I need, such as Add () and Remove (), EF just does not recognize the navigation property.

Are there correct ways (as in: low database interaction) of queries through children (and what are they)? Or is it simply impossible?

Answer 1

The workaround that I found began in the middle, in children:

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

int cnt = gc.Count();

At the very least, this gives an sql query that returns only the number of grandchildren, and not all intermediate data.