Map category parent id self referencing table structure to EF Core entity

Question

Database Table:

I tried this approach to map the category table to EF core:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Category>(entity =>
    {
        entity
            .HasMany(e => e.Children)
            .WithOne(e => e.Parent) 
            .HasForeignKey(e => e.ParentId);
    });
}

Entity:

[Table("Category"]
public class Category : EntityBase
{
    [DataType(DataType.Text), MaxLength(50)]
    public string Name { get; set; }

    public int? ParentId { get; set; }

    public int? Order { get; set; }

    [ForeignKey("ParentId")]
    public virtual Category Parent { get; set; }

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

Then in the repository:

public override IEnumerable<Category> GetAll()
{ 
    IEnumerable<Category> categories = Table.Where(x => x.Parent == null).Include(x => x.Children).ThenInclude(x=> x.Children);
    return categories;
}

This worked but anything after 3 levels was not returned no matter how many times you call Include() or ThenInclude().

I ended up writing the code myself to populate the child categories with a recursive function:

public override IEnumerable<Category> GetAll()
{
    IEnumerable<Category> categories = Table.Where(x => x.Parent == null).ToList();
    categories = Traverse(categories);
    return categories;
}

private IEnumerable<Category> Traverse(IEnumerable<Category> categories)
{
    foreach(var category in categories)
    {
        var subCategories = Table.Where(x => x.ParentId == category.Id).ToList();
        category.Children = subCategories;
        category.Children = Traverse(category.Children).ToList();
    }
    return categories;
}

Does anyone know a better way to write a stored procedure to get the table hierarchy and map to the Category entity I have provided in the example?

Answer 1

EF (and LINQ in general) has issues loading tree like data due to lack of recursive expression/CTE support.

But in case you want to load the whole tree (as opposed to filtered tree branch), there is a simple Include based solution. All you need is a single Include and then the EF navigation property fixup will do the work for you. And when you need to get only the root nodes as in your sample, the trick is to apply the filter after the query has been materialized (and navigation properties being fixed) by switching to LINQ to Objects context (using AsEnumerable() as usual).

So the following should produce the desired result with single SQL query:

public override IEnumerable<Category> GetAll()
{ 
    return Table
       .AsEnumerable()
       .Where(x => x.ParentId == null)
       .ToList();
}