How does Entity Framework work with recursive hier

Question

I have an Item. Item has a Category.

Category has ID, Name, Parent and Children. Parent and Children are of Category too.

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

@parliament gave me an idea for EF6. Example for Category with Methods to load all parents up to root node and all children.

NOTE: Use this only for non performance critical operation. Example with 1000 nodes performance from http://nosalan.blogspot.se/2012/09/hierarchical-data-and-entity-framework-4.html.

Loading 1000 cat. with navigation properties took 15259 ms 
Loading 1000 cat. with stored procedure took 169 ms

Code:

public class Category 
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public string Name { get; set; }

    public int? ParentId { get; set; }

    public virtual Category Parent { get; set; }

    public virtual ICollection<Category> Children { get; set; }

    private IList<Category> allParentsList = new List<Category>();

    public IEnumerable<Category> AllParents()
    {
        var parent = Parent;
        while (!(parent is null))
        {
            allParentsList.Add(parent);
            parent = parent.Parent;
        }
        return allParentsList;
    }

    public IEnumerable<Category> AllChildren()
    {
        yield return this;
        foreach (var child in Children)
        foreach (var granChild in child.AllChildren())
        {
            yield return granChild;
        }
    }   
}

Answer 2

You could also create a tablevalued function in the database and add that to your DBContext. Then you can call that from your code.

This example requires that you import EntityFramework.Functions from nuget.

public class FunctionReturnType
{
    public Guid Id { get; set; } 

    public Guid AnchorId { get; set; } //the zeroPoint for the recursion

    // Add other fields as you want (add them to your tablevalued function also). 
    // I noticed that nextParentId and depth are useful
}

public class _YourDatabaseContextName_ : DbContext
{
    [TableValuedFunction("RecursiveQueryFunction", "_YourDatabaseContextName_")]
    public IQueryable<FunctionReturnType> RecursiveQueryFunction(
        [Parameter(DbType = "boolean")] bool param1 = true
    )
    {
        //Example how to add parameters to your function
        //TODO: Ask how to make recursive queries with SQL 
        var param1 = new ObjectParameter("param1", param1);
        return this.ObjectContext().CreateQuery<FunctionReturnType>(
            $"RecursiveQueryFunction(@{nameof(param1)})", param1);
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //add both (Function returntype and the actual function) to your modelbuilder. 
        modelBuilder.ComplexType<FunctionReturnType>();
        modelBuilder.AddFunctions(typeof(_YourDatabaseContextName_), false);

        base.OnModelCreating(modelBuilder);
    }

    public IEnumerable<Category> GetParents(Guid id)
    {
        //this = dbContext
        return from hierarchyRow in this.RecursiveQueryFunction(true)
            join yourClass from this.Set<YourClassThatHasHierarchy>()
            on hierarchyRow.Id equals yourClass.Id
            where hierarchyRow.AnchorId == id
            select yourClass;
    }
}

Answer 3

Instead of using the Include 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 4

If you definitely want the whole hierarchy loaded, then if it was me I'd try writing a stored procedure 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 procedure 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 stored procedure call, so no MARS problems either.

Hope this helps

Alex

Answer 5

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 6

You don't want to do recursive loading of the hierarchy, unless you are allowing a user to iteratively drill down/up the tree: Every level of recursion is another trip to the database. Similarly, you'll want lazy loading off to prevent further DB trips as you're traversing the hierarchy when rendering to a page or sending over a webservice.

Instead, flip your query: Get Catalog, and Include the items in it. This will get you all items both hierarchically (navigation properties) and flattened, so now you just need to exclude the non-root elements present at the root, which should be pretty trivial.

I had this problem and provided a detailed example of this solution to another, here