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

Question

Explanation why this question is different to: EF - multiple includes to eager load hierarchical data. Bad practice?

1. the possible duplicate is an opinion based question if this is a bad practice or not whereas my question tends to get the technical solution on how to do it, independent of the opinion if it is a good practice or not. I leave this decision up to the product owner, requirement engineer, project manager and the costumer who wants that feature.
2. The given answers either explain why it is a bad practice or use an approach which is not working for me (using Include() and ThenInclude() produces a hard coded depth whereas I need a flexible depth).

---

In the current project (a .NET core web api) I try to load a hierarchy from a self referencing table.

After googling a lot I was surprised that such a task (which I thought would be trivial) seems not to be trivial.

Well, I have this table to form my hierarchy:

CREATE TABLE [dbo].[Hierarchy] (
    [Id]        INT           IDENTITY (1, 1) NOT NULL,
    [Parent_Id] INT           NULL,
    [Name]      NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Hierarchy_Hierarchy] FOREIGN KEY ([Parent_Id]) REFERENCES [dbo].[Hierarchy] ([Id])
);

In the web api I try to return the complete hierarchy. One maybe special thing (that could help) is the fact that I want to load the complete table.

I also know that I could use eager loading and the navigation property (Parent and InverseParent for children)

_dbContext.Hierarchy.Include(h => h.InverseParent).ThenInclude(h => h.InverseParent)...

The problem with that is that this would load a hard coded depth (e.g. six levels if I use 1 Include() and 5 ThenInclude()) but my hierarchy has a flexible depth.

Can anyone help me out by giving me some code how to load the full table (e.g. into memory in an optimal scenario with 1 DB call) and then make the method return the full hierarchy?

Answer 1

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.