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!
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.
EF Core 6.0 itself is 31% faster executing queries. Heap allocations have been reduced by 43%.
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.
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));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With