How to load nested Entity Framework objects efficiently

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 cod开发者_运维技巧e 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!)

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();

When dealing with potentially complex hierarchies, it can help to pull a simplified structure of the data you need. This will avoid the need to eager-load as the data will be joined in the results, loading what is necessary, and typically can take better advantage of indexes on the database server side.

For example if you want to load "basic" details on categories, sub-categories, and products suitable for display in a tree structure:

var results = dbContext.Categories.Where(c=> /*some condition*/)
    .Select(c=> new {c.CategoryId, c.Name, SubCategories = 
        c.SubCategories.Select(sc=> new { sc.SubCategoryId, sc.Name, Products = sc.Products.Select(p=> new {p.ProductId, p.Name}) }) }).ToList();

Granted, it may not look pretty, but no magic strings for eager load expressions, and the SQL that EF generates will typically be several orders more efficient & utilize indexes that you'd typically want on those tables.

For simple entities you can use this to .Select down through the child hierarchy, but when dealing with heavier entities where you just want a few key details this can save quite a bit of processing and network bandwidth by only retrieving and transmitting the data you need.