How Does Entity Framework Work with Recursive Hierarchies? Include() Seems Not to Work with It

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

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

loading a full hierarchy from a self referencing table with EntityFramework.Core

In fact loading the whole hierarchy is quite easy thanks to the so called EF (Core) relationship fixup.

Let say we have the following model:

public class Hierarchy
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Hierarchy Parent { get; set; }
    public ICollection<Hierarchy> Children { get; set; }
}

Then the following code

var hierarchy = db.Hierarchy.Include(e => e.Children).ToList();

will load the whole hierarchy with correctly populated Parent and Children properties.

The problem described in the referenced posts arise when you need to load just part of the hierarchy, which is hard due to the lack of CTE like support in LINQ.

Entity Framework Extensions Recursive Query

Disclaimer: I'm the owner of Entity Framework Extensions

You got it right, we indeed use a CTE in the generated SQL.

Here is how looks the SQL template:

WITH
    SelfHierarchyQueryOuter AS (@(sqlMaster)),
    SelfHierarchyQueryInner AS (@(sqlHierarchy)),
    SelfHierarchyQuery AS (SELECT A.*, 0 AS ZZZ_Recursion FROM (SELECT * FROM SelfHierarchyQueryOuter) AS A 
                                    UNION ALL
                                    SELECT B.*, ZZZ_Recursion + 1 AS ZZZ_Recursion FROM  (SELECT * FROM SelfHierarchyQueryInner) AS B
                                    INNER JOIN SelfHierarchyQuery AS C ON @(keyJoins) 
                                    WHERE ZZZ_Recursion < @(maxRecursion)
                                    )

@(selectFinal)
FROM    SelfHierarchyQuery

So a query like this one:

var list2 = context.EntitySimples.ToSelfHierarchyList(x => x.Parent, options => options.MaxRecursion = 5);

Will generate the following SQL:

WITH
    SelfHierarchyQueryOuter AS (SELECT TOP 100 PERCENT  
    [Extent1].[ID] AS [ID], 
    [Extent1].[ColumnInt1] AS [ColumnInt1], 
    [Extent1].[ColumnInt2] AS [ColumnInt2], 
    [Extent1].[Parent_ID] AS [Parent_ID]
    FROM [dbo].[EntitySimple] AS [Extent1]),
    SelfHierarchyQueryInner AS (SELECT TOP 100 PERCENT  
    [Extent1].[ID] AS [ID], 
    [Extent1].[ColumnInt1] AS [ColumnInt1], 
    [Extent1].[ColumnInt2] AS [ColumnInt2], 
    [Extent1].[Parent_ID] AS [Parent_ID]
    FROM [dbo].[EntitySimple] AS [Extent1]),
    SelfHierarchyQuery AS (SELECT A.*, 0 AS ZZZ_Recursion FROM (SELECT * FROM SelfHierarchyQueryOuter) AS A 
                                    UNION ALL
                                    SELECT B.*, ZZZ_Recursion + 1 AS ZZZ_Recursion FROM  (SELECT * FROM SelfHierarchyQueryInner) AS B
                                    INNER JOIN SelfHierarchyQuery AS C ON C.[Parent_ID] = B.[ID] 
                                    WHERE ZZZ_Recursion < 5
                                    )

SELECT *
FROM    SelfHierarchyQuery

Nothing "special" here, just using one great feature of the CTE to make it works.

---

Related Topics