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

asp.net-core c# entity-framework-core linq


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,
    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?

5/23/2017 12:01:37 PM

Accepted Answer

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.

1/24/2017 7:51:16 PM

Popular Answer

I have built stored procedure to get all childs in all levels using recursive cte stored procedure to get all childs ids of any level of self-referencing table using Entity Framework and recursive cte

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow