将类别父 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;
}

这有效，但无论您调用 Include() 或 ThenInclude() 多少次，都不会返回 3 个级别之后的任何内容.

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?

推荐答案

由于缺乏递归表达式/CTE 支持，EF(以及一般的 LINQ)在加载树状数据时存在问题.

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