使用实体框架自我参照树的最有效的方法 [英] Most efficient method of self referencing tree using Entity Framework

问题描述

所以我有一个SQL表基本上是

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.

不幸的是EF，我不认为玩弄自引用表超过1平深不错。所以我留下了几个选项

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）深入创建查询，订单，然后C＃创建自定义类，一次填充它一个深度。

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

2）找到某种方式来急于负载EF中的数据，我不认为这是可能的无限量的水平，只有一个固定的量。

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）我什至不知道其它的一些方式。

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

任何投入将受到欢迎！

Any inputs would be welcomed!

推荐答案

我已经成功地映射分层数据使用EF

I have successfully mapped hierarchical data using EF.

就拿一个建立实体。这可以重新present公司，大学或其他单位更大的组织结构中：

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

请注意，到目前为止，我还没有包括你的天堂或深度属性。你说得对，EF不会产生上述关系的嵌套层次查询工作。我最后决定是增加了新的动名词的实体，2个新的实体属性一起：

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; }

}

在写这件事， hazzik发布一个答案，这是非常相似，这种做法。我会继续写下去了，虽然，提供一个稍微不同的选择。我喜欢让我的祖孙动名词类型的实际实体类型，因为它可以帮助我得到祖孙之间的分离（你提到以什么作为深度）。这里是我如何映射这些：

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);

另外，我没有使用一个存储过程来更新节点的映射。相反，我们有一组会得出/计算祖先，并根据家长和放后代性质的内部命令;儿童属性。但最终，你最终能够做一些非常相似的查询在hazzik的回答是：

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);

的原因主要实体和它的祖先之间的桥梁实体/后代又是因为这个实体可以让你得到分离。此外，通过声明它作为一个确定的关系，可以从集合中删除节点，而无需显式调用DbContext.Delete（）在他们身上。

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屋！