Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Most efficient method of self referencing tree using Entity Framework

So I have a SQL table which is basically

ID, ParentID, MenuName, [Lineage, Depth] 

The last two columns are auto-computed to help with searching so we can ignore them for now.

I'm creating a drop down menu system with multiple categories.

Unfortunately EF I don't think plays nice with Self referencing tables more than 1 level deep. So I'm left with a few options

1) Create query, order by depth and then create a custom class in C#, populating it one depth at a time.

2) Find some way to eager load the data in EF, I don't think it is possible for an unlimited amount of levels, only a fixed amount.

3) Some other way I'm not even sure about.

Any inputs would be welcomed!

like image 239
John Mitchell Avatar asked Jul 19 '12 16:07

John Mitchell


People also ask

Which technique improves the performance mostly in Entity Framework?

The AsNoTracking method tells Entity Framework to stop that additional work and so, it can improve the performance of your application. So, in theory, a query with AsNoTracking should perform better than without.

Is EF core faster than EF6?

EF Core 6.0 itself is 31% faster executing queries. Heap allocations have been reduced by 43%.

What is the difference between EF6 and EF core?

Keep using EF6 if the data access code is stable and not likely to evolve or need new features. Port to EF Core if the data access code is evolving or if the app needs new features only available in EF Core. Porting to EF Core is also often done for performance.


1 Answers

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)); 
like image 156
danludwig Avatar answered Sep 18 '22 11:09

danludwig