title: Map category parent id self referencing table structure to EF Core entity
body: <p>Database Table:</p>
  <p><a href="https://i.stack.imgur.com/MX0Qc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/MX0Qc.png" alt="enter image description here" /></a></p>
  <p>I tried this approach to map the category table to EF core:</p>
  <pre class="lang-c# prettyprint-override"><code>protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      modelBuilder.Entity&lt;Category&gt;(entity =&gt;
      {
          entity
              .HasMany(e =&gt; e.Children)
              .WithOne(e =&gt; e.Parent) 
              .HasForeignKey(e =&gt; e.ParentId);
      });
  }
  </code></pre>
  <p>Entity:</p>
  <pre class="lang-c# prettyprint-override"><code>[Table(&quot;Category&quot;]
  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(&quot;ParentId&quot;)]
      public virtual Category Parent { get; set; }
  
      public virtual ICollection&lt;Category&gt; Children { get; set; }
  }
  </code></pre>
  <p>Then in the repository:</p>
  <pre class="lang- prettyprint-override"><code>public override IEnumerable&lt;Category&gt; GetAll()
  { 
      IEnumerable&lt;Category&gt; categories = Table.Where(x =&gt; x.Parent == null).Include(x =&gt; x.Children).ThenInclude(x=&gt; x.Children);
      return categories;
  }
  </code></pre>
  <p>This worked but anything after 3 levels was not returned no matter how many times you call Include() or ThenInclude().</p>
  <p>I ended up writing the code myself to populate the child categories with a recursive function:</p>
  <pre class="lang-c# prettyprint-override"><code>public override IEnumerable&lt;Category&gt; GetAll()
  {
      IEnumerable&lt;Category&gt; categories = Table.Where(x =&gt; x.Parent == null).ToList();
      categories = Traverse(categories);
      return categories;
  }
  
  private IEnumerable&lt;Category&gt; Traverse(IEnumerable&lt;Category&gt; categories)
  {
      foreach(var category in categories)
      {
          var subCategories = Table.Where(x =&gt; x.ParentId == category.Id).ToList();
          category.Children = subCategories;
          category.Children = Traverse(category.Children).ToList();
      }
      return categories;
  }
  </code></pre>
  <p>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?</p>