How to load nested Entity Framework objects efficiently

Question

I'm trying to load a 3-deep tree into a treeview control using entity framework

Categories, SubCategories and Products in the code below categories is an IQueryable<ProductCategory> where ProductCategory is an EF4.0 generated object (default code generation)

SubCategories is a FK back to the categories table (so in theory we could go to any depth, but the data domain will only have two levels)

var container = categories.Where(c=>c.somecondition).Include("Subcategories.Products");
foreach (var cat in container) {
  addtoTree(cat);
  foreach (var sub in cat.SubCategories)
  {
     addtoTree(sub);
     foreach (var prod in sub.Products) addtoTree(prod);

  }
}

This is still issuing queries for each inner loop iteration. Am I missing something in the EF configuration (changes to the context?) to stop that happening? Or is there another way of writing this sort of code?

(As a horrendous hack for now, I've created an SQL view that flattens the information, and I iterate that to re-build the nested objects by hand...nasty, but fast!)

Answer 1

Try to eagerly execute the query before looping by calling .ToList() at the end:

var container = categories
    .Where(c => c.somecondition)
    .Include("Subcategories.Products")
    .ToList();