How to do recursive load with Entity framework?

Question

Hi,

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?

Thanks Avi

Answer 1

When you use Include(), you are asking the Entity Framework to translate your query into SQL. So think: How would you write an SQL statement which returns a tree of an arbitrary depth?

Answer: Unless you are using specific hierarchy features of your database server (which are not SQL standard, but supported by some servers, such as SQL Server 2008, though not by its Entity Framework provider), you wouldn't. The usual way to handle trees of arbitrary depth in SQL is to use the nested sets model rather than the parent ID model.

Therefore, there are three ways which you can use to solve this problem:

1. Use the nested sets model. This requires changing your metadata.
2. Use SQL Server's hierarchy features, and hack the Entity Framework into understanding them (tricky, but this technique might work). Again, you'll need to change your metadata.i
3. Use explicit loading or EF 4's lazy loading instead of eager loading. This will result in many database queries instead of one.