EF4 querying through the generations
- by Hans Kesting
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 it's grandchildren.
Changing the type of the Children property from ICollection to IQueryable doesn't solve this. Apart from missing needed methods like Add() and Remove(), EF just doesn't recognize the 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?