将类别父ID自引用表结构映射到EF Core实体 [英] Map category parent id self referencing table structure to EF Core entity

问题描述

数据库表：

图片

我尝试了这种方法将类别表映射到EF核心：

I tried this approach to map the category table to EF core:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Category>(entity =>
    {
        entity
            .HasMany(e => e.Children)
            .WithOne(e => e.Parent) 
            .HasForeignKey(e => e.ParentId);
    });
}

实体：

[Table("Category"]
public class Category : EntityBase
{
    [DataType(DataType.Text), MaxLength(50)]
    public string Name { get; set; }

    public int? ParentId { get; set; }

    public int? Order { get; set; }

    [ForeignKey("ParentId")]
    public virtual Category Parent { get; set; }

    public virtual ICollection<Category> Children { get; set; }
}

然后在存储库中：

public override IEnumerable<Category> GetAll()
{ 
    IEnumerable<Category> categories = Table.Where(x => x.Parent == null).Include(x => x.Children).ThenInclude(x=> x.Children);
    return categories;
}

此方法有效，但在3个级别后均未返回不论您调用Include（）或ThenInclude（）有多少次。

This worked but anything after 3 levels was not returned no matter how many times you call Include() or ThenInclude().

我最终自己编写了代码，以使用递归函数填充子类别：

I ended up writing the code myself to populate the child categories with a recursive function:

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

private IEnumerable<Category> Traverse(IEnumerable<Category> categories)
{
    foreach(var category in categories)
    {
        var subCategories = Table.Where(x => x.ParentId == category.Id).ToList();
        category.Children = subCategories;
        category.Children = Traverse(category.Children).ToList();
    }
    return categories;
}

有谁知道一种更好的方式来编写存储过程以获取表层次结构并映射到示例中提供的Category实体？

Does anyone know a better way to write a stored procedure to get the table hierarchy and map to the Category entity I have provided in the example?

推荐答案

EF（通常是LINQ）在加载树时遇到问题数据由于缺少递归表达式/ CTE支持。

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

但是如果您要加载整个树（而不是过滤的树分支） ），有一个简单的基于 Include 的解决方案。您只需要一个 Include ，然后EF导航属性修复程序即可为您完成工作。当您仅需要获取示例中的根节点时，诀窍是在实现查询（并且导航属性已固定）之后通过切换到LINQ to Objects上下文（使用像往常一样为AsEnumerable（）。）

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

因此，以下代码应通过单个SQL查询产生所需的结果：

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

这篇关于将类别父ID自引用表结构映射到EF Core实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！