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!)

Found the additional bug - if your code (in my case burried in the addtoTree(sub)) tries to reference back up the tree you need to include that in the Include statement - i.e. Include("Subcategories.Products.Subcategories") (in this case a many-many relationship) — Andiih
– Andiih, Commented Sep 1, 2011 at 19:27

Darin Dimitrov · Accepted Answer · 2011-09-01 17:48:11Z

2

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

answered Sep 1, 2011 at 17:48

Darin Dimitrov

1.0m275 gold badges3.3k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andiih Over a year ago

I added the ToList to no avail: I've edited the question with more details that the Categories and Subcategories are the same table...it may be relevant (should have put that in originally)

Andiih Over a year ago

actually your example does of course work perfectly on the simplified example I posted...I'll try to figure out what's actually different in my real world example!

Steve Py · Accepted Answer · 2017-06-06 04:44:16Z

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.

Collectives™ on Stack Overflow

How to load nested Entity Framework objects efficiently

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related