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

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

I found out that if you include "two parent levels", you will get the whole parent hierarchy, like that:

var query = Context.Items
            .Include(i => i.Category)
            .Include(i => i.Category.Parent.Parent)

And now for a completely different approach to hierarchical data, for example populating a treeview.

First, do a flat query for all data, and then build the object graph in memory:

  var items = this.DbContext.Items.Where(i=> i.EntityStatusId == entityStatusId).Select(a=> new ItemInfo() { 
            Id = a.Id,
            ParentId = a.ParentId,
            Name = a.Name,
            ItemTypeId = a.ItemTypeId
            }).ToList();

Get the root item:

 parent = items.FirstOrDefault(a => a.ItemTypeId == (int)Enums.ItemTypes.Root);

Now build your graph:

 this.GetDecendantsFromList(parent, items);


 private void GetDecendantsFromList(ItemInfo parent, List<ItemInfo> items)
    {
        parent.Children = items.Where(a => a.ParentId == parent.Id).ToList();
        foreach (var child in parent.Children)
        {
            this.GetDecendantsFromList(child,items);
        }
    }

My suggestion would be

var query = CreateQuery()
    .Where(entity => entity.Id == Id)
    .Include(entity => entity.Parent);
var result = await FindAsync(query);

return result.FirstOrDefault();

and it means it will load single entity and all this entity.Parent entities recursive.

entity is same as entity.Parent

public static class EntityFrameworkExtensions
{
    public static ObjectContext GetObjectContext(this DbContext context) 
    {
        ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;

        return objectContext;
    }

    public static string GetTableName<T>(this ObjectSet<T> objectSet) 
        where T : class
    {
        string sql = objectSet.ToTraceString();
        Regex regex = new Regex("FROM (?<table>.*) AS");
        Match match = regex.Match(sql);

        string table = match.Groups["table"].Value;
        return table;
    }

    public static IQueryable<T> RecursiveInclude<T>(this IQueryable<T> query, Expression<Func<T, T>> navigationPropertyExpression, DbContext context)
        where T : class
    {
        var objectContext = context.GetObjectContext();

        var entityObjectSet = objectContext.CreateObjectSet<T>();
        var entityTableName = entityObjectSet.GetTableName();
        var navigationPropertyName = ((MemberExpression)navigationPropertyExpression.Body).Member.Name;

        var navigationProperty = entityObjectSet
            .EntitySet
            .ElementType
            .DeclaredNavigationProperties
            .Where(w => w.Name.Equals(navigationPropertyName))
            .FirstOrDefault();

        var association = objectContext.MetadataWorkspace
            .GetItems<AssociationType>(DataSpace.SSpace)
            .Single(a => a.Name == navigationProperty.RelationshipType.Name);

        var pkName = association.ReferentialConstraints[0].FromProperties[0].Name;
        var fkName = association.ReferentialConstraints[0].ToProperties[0].Name;

        var sqlQuery = @"
                EXEC ('
                    ;WITH CTE AS
                    (
                        SELECT 
                            [cte1].' + @TABLE_PK + '
                            , Level = 1
                        FROM ' + @TABLE_NAME + ' [cte1]
                        WHERE [cte1].' + @TABLE_FK + ' IS NULL

                        UNION ALL

                        SELECT 
                            [cte2].' + @TABLE_PK + '
                            , Level = CTE.Level + 1
                        FROM ' + @TABLE_NAME + ' [cte2]
                            INNER JOIN CTE ON CTE.' + @TABLE_PK + ' = [cte2].' + @TABLE_FK + '
                    )
                    SELECT 
                        MAX(CTE.Level)
                    FROM CTE 
                ')
            ";

        var rawSqlQuery = context.Database.SqlQuery<int>(sqlQuery, new SqlParameter[]
            {
                new SqlParameter("TABLE_NAME", entityTableName),
                new SqlParameter("TABLE_PK", pkName),
                new SqlParameter("TABLE_FK", fkName)
            });

        var includeCount = rawSqlQuery.FirstOrDefault();

        var include = string.Empty;

        for (var i = 0; i < (includeCount - 1); i++)
        {
            if (i > 0)
                include += ".";

            include += navigationPropertyName;
        }

        return query.Include(include);
    }
}

Let me offer my simple solution that fits needs to enable/disable the branch of hierarchical data of the selected department's structure of an organization.

The table Departments looks according this SQL

CREATE TABLE [dbo].[Departments](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](1000) NOT NULL,
    [OrganizationID] [int] NOT NULL,
    [ParentID] [int] NULL,
    [IsEnabled] [bit] NOT NULL, 
 CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

C# code provides a very simple approach that work fine for me. 1. It returns the complete table asynchronously. 2. It changes property for the linked rows.

public async Task<bool> RemoveDepartmentAsync(int orgID, int depID)
            {
                try
                {
                    using (var db = new GJobEntities())
                    {
                        var org = await db.Organizations.FirstOrDefaultAsync(x => x.ID == orgID); // Check if  the organization exists
                        if (org != null)
                        {
                            var allDepartments = await db.Departments.ToListAsync(); // get all table items
                            var isExisting = allDepartments.FirstOrDefault(x => x.OrganizationID == orgID && x.ID == depID);
                            if (isExisting != null) // Check if the department exists
                            {
                                isExisting.IsEnabled = false; // Change the property of visibility of the department
                                var all = allDepartments.Where(x => x.OrganizationID == orgID && x.ID == isExisting.ID).ToList();
                                foreach (var item in all)
                                {
                                    item.IsEnabled = false;
                                    RecursiveRemoveDepartment(orgID, item.ID, ref allDepartments); // Loop over table data set to change property of the linked items
                                }
                                await db.SaveChangesAsync();
                            }
                            return true;
                        }
                    }
                }
                catch (Exception ex)
                {
                    logger.Error(ex);
                }

                return false;
            }

            private void RecursiveRemoveDepartment(int orgID, int? parentID, ref List<Department> items)
            {
                var all = items.Where(x => x.OrganizationID == orgID && x.ParentID == parentID);
                foreach (var item in all)
                {
                    item.IsEnabled = false;
                    RecursiveRemoveDepartment(orgID, item.ID, ref items);
                }
            }

This approach works very fast for relative small amount of records I guess less 100000. Probably for big set of data you have to implement server side stored function.

Enjoy!