How does Entity Framework work with recursive hierarchies? Include() seems not to work with it.

Question

I have an Item. Item has a Category.

Category has ID, Name, Parent and Children. Parent and Children are categories also.

When I do a linq to entities query for a specific Item, it doesn't return the related Category, unless I use the Include("Category") method. But it doesn't bring the full category, with its parent and children. I could do Include("Category.Parent"), but this object is something like a tree, I have a recursive hierarchy and I don't know where it ends.

How can I make EF fully load the Category, with parent and children, and the parent with their parent and children, and so on?

This is not something for the whole application, for performance considerations it would be needed only for this specific entity, the Category.

Answer 1

You chould rather introduce a mapping table that maps each Category a parent and a child, instead of adding the parent and child property to the cargo itself.

Depending on how often you need that information it can be queried on demand. Via unique constraints in the db you can avoid an infinite amount of relationships beeing possible.

Answer 2

Instead of using the "Inculde" method you could use "Load".

You could then do a for each and loop through all the children loading their children. Then do a for each through their children, and so on.

The number of levels down you go will be hard coded in the number of for each loops you have.

Here is an example of using load: http://msdn.microsoft.com/en-us/library/bb896249.aspx

Answer 3

It could be dangerous if you did happen to load all recursive entities, especially on category, you could end up with WAY more than you bargained for:

Category > Item > OrderLine > Item
                  OrderHeader > OrderLine > Item
         > Item > ...

All of a sudden you've loaded most of your database, you could have also loaded invoices lines, then customers, then all their other invoices.

What you should do is something like the following:

var qryCategories = from q in ctx.Categories
                    where q.Status == "Open"
                    select q;

foreach (Category cat in qryCategories) {
    if (!cat.Items.IsLoaded)
        cat.Items.Load();
    // This will only load product groups "once" if need be.
    if (!cat.ProductGroupReference.IsLoaded)
        cat.ProductGroupReference.Load();
    foreach (Item item in cat.Items) {
        // product group and items are guaranteed
        // to be loaded if you use them here.
    }
}

A better solution however is to construct your query to build an anonymous class with the results so you only need to hit your datastore once.

var qryCategories = from q in ctx.Categories
                    where q.Status == "Open"
                    select new {
                        Category = q,
                        ProductGroup = q.ProductGroup,
                        Items = q.Items
                    };

This way you could return a dictionary result if required.

Remember, your contexts should be as short lived as possible.

Answer 4

Wouldn't it be much simpler to create a view that is a recursive query ala http://www.webinade.com/web-development/creating-recursive-sql-calls-for-tables-with-parent-child-relationships and simply map that. Much faster too.

Answer 5

If you definitely want the whole hierarchy loaded, then if it was me I'd try writing a stored proc, who's job it is to return all the items in a hierarchy, returning the one you ask for first (and its children subsequently).

And then let the EF's relationship fixup ensure that they are all hooked up.

i.e. something like:

// the GetCategoryAndHierarchyById method is an enum
Category c = ctx.GetCategoryAndHierarchyById(1).ToList().First();

if you've written your stored proc correctly, materializing all the items in the hierarchy (i.e. ToList()) should make EF relationship fixup kicks in.

And then the item you want (First()) should have all its Children loaded and they should have their children loaded etc. All be populated from that one Store Proc call, so no MARS problems either.

Hope this helps

Alex