使用实体框架自动引用树的最有效的方法 [英] Most efficient method of self referencing tree using Entity Framework
问题描述
所以我有一个SQL表，基本上是
ID，ParentID，MenuName，[Lineage，Depth]
最后两列是自动计算的，以帮助搜索，所以我们现在可以忽略它们。
我正在创建一个包含多个类别的下拉菜单系统。
不幸的是，EF我觉得不喜欢使用Self参考表超过1级深度。所以我有几个选项
1）创建查询，按深度排序，然后在C＃中创建一个自定义类，一次填充一个深度。 2）找到一些方法来加载EF中的数据，我不认为无限量的级别是可能的，只有一个固定的数量。
3）其他一些我甚至不确定的方式。
任何投入都将受到欢迎！
我已经使用EF成功映射了分层数据。
例如建立实体。这可以代表公司，大学或更大组织结构中的其他单位：
public class Establishment：Entity
{
public string Name {get;组; }
public virtual Establishment Parent {get;组; }
public virtual ICollection< Establishment>孩子{get;组; }
...
}
这是父/子属性被映射。这样，当您设置1个实体的父项时，父实体的儿童集合将自动更新：
// ParentEstablishment 0。 .1 - * ChildEstablishment
HasOptional（d => d.Parent）
.WithMany（p => p.Children）
.Map（d => d.MapKey（ParentId） ）
.WillCascadeOnDelete（false）; //当父项被删除时不要删除孩子
请注意，到目前为止，我还没有包括你的血统或深度属性。你是对的，EF不能很好地用于生成具有上述关系的嵌套层次查询。我最终定下的是添加了一个新的gerund实体，以及两个新的实体属性：
public class EstablishmentNode：Entity
{
public int AncestorId {get;组; }
public virtual Establishment Ancestor {get;组; }
public int OffspringId {get;组; }
public virtual Establishment后代{get;组; }
public int Separation {get;组; }
}
public class建立：Entity
{
...
public virtual ICollection< EstablishmentNode>祖先{get;组; }
public virtual ICollection< EstablishmentNode>后代{get;组;
}
在写这个文件时， hazzik发布了一个非常类似于这种方法的答案。我会继续写作，提供略有不同的选择。我喜欢让我的祖先和子孙gerund类型实际的实体类型，因为它有助于我获得祖先和后代之间的分离（你所谓的深度）。以下是我如何映射这些：
private class EstablishmentNodeOrm：EntityTypeConfiguration< EstablishmentNode>
{
internal EstablishmentNodeOrm（）
{
ToTable（typeof（EstablishmentNode）.Name）;
HasKey（p => new {p.AncestorId，p.OffspringId}）;
}
}
...最后，建立实体：
//有很多祖先
HasMany（p => p.Ancestors）
.WithRequired（d => d.Offspring）
.HasForeignKey（d => d.OffspringId）
.WillCascadeOnDelete（false）;
//有很多后代
HasMany（p => p.Offspring）
.WithRequired（d => d.Ancestor）
.HasForeignKey（d => d.AncestorId）
.WillCascadeOnDelete（false）;
此外，我没有使用sproc更新节点映射。相反，我们有一组内部命令将导出/计算基于父级和子级的祖先和后代属性。儿童财产。但是最终，你最终能够像在hazzik的答案中做一些类似的查询：
//加载实体所有的后代
var establishment = dbContext.Establishments
.Include（x => x.Offspring.Select（y => e.Offspring））
.SingleOrDefault（x => ; x.Id == id）;
主实体与其祖先/子代之间的桥接实体的原因是因为该实体让你得到分离。另外，通过将其声明为识别关系，您可以从集合中删除节点，而无需在其上显式调用DbContext.Delete（）。
//加载深度超过3级的所有实体
var establishmentments = dbContext.Establishments
.Where（x => x.Ancestors.Any（y => y.Separation > 3））;
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!
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));
这篇关于使用实体框架自动引用树的最有效的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
