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?
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 :)
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
Basically, just don't use
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
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);
subset will look like this:
-- Item1 -- Item2 -- Item3 -- Item4 -- Item5
About Craig's solutions:
- 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!!)
- I've never tried the nested set stuff, and I wouldn't suggest hacking EF configuration to make this work either, given there is a far easier solution.
- Another reasonable suggestion is creating a database view that provides the self-linking, then map that view to an intermediary join/link/m2m table. Personally, I found this solution to be more complicated than necessary, but it probably has its uses.