Loading a complete hierarchy from a self-reference table using EntityFramework.Core

Question

Explanation why this question is different from: EF - the plural includes hierarchical load load data. Bad practice?

• a possible duplicate is an opinion-based question if it is bad practice or not, while my question tends to get a technical decision on how to do this, regardless of opinion if it is good practice or not. I leave this decision to the product owner, requirements engineer, project manager and customer who wants this feature.
• These answers either explain why this is bad practice, or they use an approach that does not work for me (using Include () and ThenInclude () creates hardcoded depth, while I need flexible depth).

---

In the current project (.NET core web api) I am trying to load a hierarchy from a self-promotion table.

After the multiplayer game, I was surprised that such a task (which, as I thought, would be trivial) does not seem 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'm trying to get the full hierarchy back. Perhaps something special (that might help) is that I want to download the full table.

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

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

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

Can someone help me by giving me the code, how to load a complete table (for example, 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 entire hierarchy is quite simple thanks to the so-called EF (Core) relationship correction.

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(); 

load the entire hierarchy with the correctly populated Parent and Children properties.

The problem described in the referenced messages occurs when you need to load only part of the hierarchy, which is difficult due to the lack of CTE support in LINQ.

Answer 2

I have a built-in stored procedure for getting all children at all levels using recursive cte to get all children at any level of the self-regulation table using Entity Framework and recursive cte