How to do recursive load with Entity framework?

Question

I have a tree structure in the DB with TreeNodes table. the table has nodeId, parentId and parameterId. in the EF, The structure is like TreeNode.Children where each child is a TreeNode... I also have a Tree table with contain id,name and rootNodeId.

At the end of the day I would like to load the tree into a TreeView but I can't figure how to load it all at once. I tried:

var trees = from t in context.TreeSet.Include("Root").Include("Root.Children").Include("Root.Children.Parameter")
        .Include("Root.Children.Children")
                        where t.ID == id
                        select t;

This will get me the the first 2 generations but not more. How do I load the entire tree with all generations and the additional data?

Answer 1

I had this problem recently and stumbled across this question after I figured a simple way to achieve results. I provided an edit to Craig's answer providing a 4th method, but the powers-that-be decided it should be another answer. That's fine with me :)

My original question / answer can be found here.

This works so long as your items in the table all know which tree they belong to (which in your case it looks like they do: t.ID). That said, it's not clear what entities you really have in play, but even if you've got more than one, you must have a FK in the entity Children if that's not a TreeSet

Basically, just don't use Include():

var query = from t in context.TreeSet
            where t.ID == id
            select t;

// if TreeSet.Children is a different entity:
var query = from c in context.TreeSetChildren
            // guessing the FK property TreeSetID
            where c.TreeSetID == id
            select c;

This will bring back ALL the items for the tree and put them all in the root of the collection. At this point, your result set will look like this:

-- Item1
   -- Item2
      -- Item3
-- Item4
   -- Item5
-- Item2
-- Item3
-- Item5

Since you probably want your entities coming out of EF only hierarchically, this isn't what you want, right?

.. then, exclude descendants present at the root level:

Fortunately, because you have navigation properties in your model, the child entity collections will still be populated as you can see by the illustration of the result set above. By manually iterating over the result set with a foreach() loop, and adding those root items to a new List<TreeSet>(), you will now have a list with root elements and all descendants properly nested.

If your trees get large and performance is a concern, you can sort your return set ASCENDING by ParentID (it's Nullable, right?) so that all the root items are first. Iterate and add as before, but break from the loop once you get to one that is not null.

var subset = query
     // execute the query against the DB
     .ToList()
     // filter out non-root-items
     .Where(x => !x.ParentId.HasValue);

And now subset will look like this:

-- Item1
   -- Item2
      -- Item3
-- Item4
   -- Item5

About Craig's solutions:

1. You really don't want to use lazy loading for this!! A design built around the necessity for n+1 querying will be a major performance sucker.
********* (Well, to be fair, if you're going to allow a user to selectively drill down the tree, then it could be appropriate. Just don't use lazy loading for getting them all up-front!!)