title: loading a full hierarchy from a self referencing table with EntityFramework.Core
body: <p>Explanation why this question is different to: <a href="https://stackoverflow.com/questions/23497079/ef-multiple-includes-to-eager-load-hierarchical-data-bad-practice">EF - multiple includes to eager load hierarchical data. Bad practice?</a></p>
  
  <ol>
  <li>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.</li>
  <li>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).</li>
  </ol>
  
  <hr>
  
  <p>In the current project (a .NET core web api) I try to load a hierarchy from a self referencing table.</p>
  
  <p>After googling a lot I was surprised that such a task (which I thought would be trivial) seems not to be trivial.</p>
  
  <p>Well, I have this table to form my hierarchy:</p>
  
  <pre><code>
  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])
  );
  </code></pre>
  
  <p>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.</p>
  
  <p>I also know that I could use eager loading and the navigation property (Parent and InverseParent for children)</p>
  
  <pre><code>
  _dbContext.Hierarchy.Include(h => h.InverseParent).ThenInclude(h => h.InverseParent)...
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>