Loading a Full Hierarchy from a Self Referencing Table with Entityframework.Core

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

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.

Load Self Referencing Entity With EF Core 5.0 ( Just get Parents and their children in their navigation property )

I found solution for this scenario.

  public async Task<IActionResult> Index()
        {

            List<Comment> comments = await _db.Comments.AsNoTrackingWithIdentityResolution()
                                                        .Include(c => c.Children)
                                                        .ToListAsync();

            // Structure Comments into a tree
            List<Comment> commentsTree = comments.Where(c => c.ParentId == null)
                                                 .AsParallel()
                                                 .ToList();

            return View(commentsTree);
        }

Eager load a self-referencing table

No, it isn't possible. Consider: All LINQ to Entities queries are translated into SQL. Which SQL statement includes an unlimited depth in a self-referencing hierarchy? In standard SQL, there isn't one. If there's an extension for this in T-SQL, I don't know what it is, and I don't think EF providers do, either.

Most efficient method of self referencing tree using Entity Framework

I have successfully mapped hierarchical data using EF.

Take for example an Establishment entity. This can represent a company, university, or some other unit within a larger organizational structure:

public class Establishment : Entity
{
    public string Name { get; set; }
    public virtual Establishment Parent { get; set; }
    public virtual ICollection<Establishment> Children { get; set; }
    ...
}

Here is how the Parent / Children properties are mapped. This way, when you set the Parent of 1 entity, the Parent entity's Children collection is automatically updated:

// ParentEstablishment 0..1 <---> * ChildEstablishment
HasOptional(d => d.Parent)
    .WithMany(p => p.Children)
    .Map(d => d.MapKey("ParentId"))
    .WillCascadeOnDelete(false); // do not delete children when parent is deleted

Note that so far I haven't included your Lineage or Depth properties. You are right, EF doesn't work well for generating nested hierarchical queries with the above relationships. What I finally settled on was the addition of a new gerund entity, along with 2 new entity properties:

public class EstablishmentNode : Entity
{
    public int AncestorId { get; set; }
    public virtual Establishment Ancestor { get; set; }

    public int OffspringId { get; set; }
    public virtual Establishment Offspring { get; set; }

    public int Separation { get; set; }
}

public class Establishment : Entity
{
    ...
    public virtual ICollection<EstablishmentNode> Ancestors { get; set; }
    public virtual ICollection<EstablishmentNode> Offspring { get; set; }

}

While writing this up, hazzik posted an answer that is very similar to this approach. I'll continue writing up though, to provide a slightly different alternative. I like to make my Ancestor and Offspring gerund types actual entity types because it helps me get the Separation between the Ancestor and Offspring (what you referred to as Depth). Here is how I mapped these:

private class EstablishmentNodeOrm : EntityTypeConfiguration<EstablishmentNode>
{
    internal EstablishmentNodeOrm()
    {
        ToTable(typeof(EstablishmentNode).Name);
        HasKey(p => new { p.AncestorId, p.OffspringId });
    }
}

... and finally, the identifying relationships in the Establishment entity:

// has many ancestors
HasMany(p => p.Ancestors)
    .WithRequired(d => d.Offspring)
    .HasForeignKey(d => d.OffspringId)
    .WillCascadeOnDelete(false);

// has many offspring
HasMany(p => p.Offspring)
    .WithRequired(d => d.Ancestor)
    .HasForeignKey(d => d.AncestorId)
    .WillCascadeOnDelete(false);

Also, I did not use a sproc to update the node mappings. Instead we have a set of internal commands that will derive / compute the Ancestors and Offspring properties based on the Parent & Children properties. However ultimately, you end up being able to do some very similar querying as in hazzik's answer:

// load the entity along with all of its offspring
var establishment = dbContext.Establishments
    .Include(x => x.Offspring.Select(y => e.Offspring))
    .SingleOrDefault(x => x.Id == id);

The reason for the bridge entity between the main entity and its Ancestors / Offspring is again because this entity lets you get the Separation. Also, by declaring it as an identifying relationship, you can remove nodes from the collection without having to explicitly call DbContext.Delete() on them.

// load all entities that are more than 3 levels deep
var establishments = dbContext.Establishments
    .Where(x => x.Ancestors.Any(y => y.Separation > 3));

Map category parent id self referencing table structure to EF Core entity

EF (and LINQ in general) has issues loading tree like data due to lack of recursive expression/CTE support.

But in case you want to load the whole tree (as opposed to filtered tree branch), there is a simple Include based solution. All you need is a single Include and then the EF navigation property fixup will do the work for you. And when you need to get only the root nodes as in your sample, the trick is to apply the filter after the query has been materialized (and navigation properties being fixed) by switching to LINQ to Objects context (using AsEnumerable() as usual).

So the following should produce the desired result with single SQL query:

public override IEnumerable<Category> GetAll()
{ 
    return Table
       .AsEnumerable()
       .Where(x => x.ParentId == null)
       .ToList();
}

How can I recursively retrieve a hierarchy of records using EF Core?

You have three options:

1. Use lazy loading. This way every time you'll access the parent group, it will automatically loaded from the database.
2. Use explicit loading for recursively load the parent groups (see explicit loading)
3. Create a view or stored procedure in the database that will return all the data you need and then create the object graph manually.

Which method is right for you depends on your unique use-case and the amount of data to load.
Options 1 and 2 are not that different from each other, but with option 2 you control when the objects are loaded. Option 3 will probably be the most efficient, but might pose problems when saving back changes (as you transform the object graph manually).

---

Related Topics