How to implement self-recursive parent-child relationship data loading with eager loading policy in Entity Framework Core 2.2?

Question

I use Entity Framework Core 2.2 and code-first approach. In database there is a table, let's simplify it as Department.

public class Department
{
    public int Id { get; set; }
    public int ? ParentId { get; set; }
    public string DepartmentName { get; set; }
    public ICollection<Department> InnerDepartments { get; set; }
}

I defined configuration with Fluent API and it generated a structure I wanted.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
        modelBuilder.Entity<Department>().HasKey(p => p.Id);
        modelBuilder.Entity<Department>().HasMany(p => p.InnerDepartments)
            .WithOne(p => p.TopDepartment)
            .HasForeignKey(p => p.TopDepartmentId);

        base.OnModelCreating(modelBuilder);
}

For example, I may have the following data.

Id  TopDepartmentId  DepartmentName
-----------------------------------------
1      NULL          Software Development
2       1            Mobile Development
3       1            Web Development
4       2            IOS Development
5       2            Android Development
6       4            Swift Development
7       4            Objective-C Development

I want to arrange data from this table to following structure:

public class DepartmentShortInfoModel
{
    public int DepartmentId { get; set; }
    public string DepartmentName { get; set; }
    public int DirectInnerDepartmentsCount { get; set; }
    public int IndirectInnerDepartmentsCount { get; set; }
}

That means that for each department customer wants to get the number of direct inner departments - (first-level-depth children count), for example, Mobile Department has 2 direct children - IOS and Android departments. In the same time, customer wants to know number of indirect inner departments, Mobile Department has 4 indirect children - IOS, Android, Swift, Objective-C, as Swift and Objective-C are children of children for Mobile Department.

I tried to implement this feature with recursive tree traversal algorithm.

private int GetChildrenDepartmentCount(
        Department department, 
        bool useRecursionForTraversal = false)
    {
        int numberOfChildrenInCurrentLevel = department.InnerDepartments?.Count() ?? 0;

        if (useRecursionForTraversal == false)
            return numberOfChildrenInCurrentLevel;

        if (folder.Children != null)
            return numberOfChildrenInCurrentLevel +
                folder.Children.Select(p => GetChildrenDepartmentCount(p, useRecursionForTraversal)).Sum();
        else return numberOfChildrenInCurrentLevel ;
    }

I load data from DbContext with Eager-loading.

using(var context = new DepartmentContext(options))
{
            var department = context.Departments.Include(p => p.InnerDepartments)
                                                .First(p => p.Id == 1);
}

However, problem is that EF loads only 1-st level children (Mobile and Web) and for the following levels returns NULL in InnerDepartments property.

How can I request EF to load inner-levels too? I read similar questions where community suggested to use SelectMany , but problem here is in NULL on InnerDepartments property, not in getting inner values with LINQ query.

The only standard efficient way of eager loading all levels is to load the whole table in memory. See https://stackoverflow.com/questions/46160780/map-category-parent-id-self-referencing-table-structure-to-ef-core-entity/46161259#46161259. Of course you can always go with SP/raw SQL using recursive CTE or whatever hierarchical SQL query construct is supported by the target database(s). — Ivan Stoev, Jun 21 '19 at 06:57

jdweng · Accepted Answer · 2019-06-21T10:23:54.483

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication116
{
    class Program
    {
        static DataTable dt;
        static void Main(string[] args)
        {
            dt = new DataTable();
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("TopDepartmentId", typeof(int));
            dt.Columns.Add("DepartmentName", typeof(string));

            dt.Rows.Add(new object[] { 1, null, "Software Development" });
            dt.Rows.Add(new object[] { 2, 1, "Mobile Development" });
            dt.Rows.Add(new object[] { 3, 1, "Web Development" });
            dt.Rows.Add(new object[] { 4, 2, "IOS Development" });
            dt.Rows.Add(new object[] { 5, 2, "Android Development" });
            dt.Rows.Add(new object[] { 6, 4, "Swift Development" });
            dt.Rows.Add(new object[] { 7, 4, "Objective-C Development" });

            GetTree(null, 0);
            Console.ReadLine();
        }
        static void GetTree(int? parent, int level)
        {
            DataRow[] children = dt.AsEnumerable().Where(x => x.Field<int?>("TopDepartmentId") == parent).ToArray();
            foreach (DataRow child in children)
            {
                int childId = child.Field<int>("Id");
                Console.WriteLine("{0}ID : '{1}', TopDepartmentId : '{2}', DepartmentName : '{3}'", 
                    new string(' ', 5 * level), //ident each level
                    childId.ToString(), 
                    (child.Field<object>("TopDepartmentId") == null) ? "BOSS" : child.Field<int?>("TopDepartmentId").ToString(),
                    child.Field<string>("DepartmentName"));

                GetTree(childId, level + 1);
            }
        }

    }

}

How to implement self-recursive parent-child relationship data loading with eager loading policy in Entity Framework Core 2.2?

1 Answers1