如何用Entity Framework Core建模查询递归树形结构？

你在将旧数据库从纯SQL迁移到EF Core时遇到了递归树形结构问题，现有表结构如下：

ProductCategory
    Id
    CategoryChidrenKey
    Name

CategoryChildren
    Id
    Key
    ChildCategoryId

关联关系：ProductCategory.CategoryChildrenKey 关联 CategoryChildren.Key，CategoryChildren.ChildCategoryId 关联 ProductCategory.Id，形成特殊的父子层级结构。以下是无需单独建模CategoryChildren表的解决方案：

一、EF Core 实体与模型配置 #

1. 定义实体类 #

直接在ProductCategory中添加父子导航属性：

public class ProductCategory
{
    public int Id { get; set; }
    public int CategoryChildrenKey { get; set; }
    public string Name { get; set; }

    // 父类别导航属性
    public ProductCategory Parent { get; set; }
    // 子类别集合导航属性
    public ICollection<ProductCategory> Children { get; set; } = new List<ProductCategory>();
}

2. 配置DbContext映射 #

在OnModelCreating中通过UsingEntity跳过中间表，直接建立父子关联：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ProductCategory>()
        .HasOne(p => p.Parent)
        .WithMany(p => p.Children)
        .UsingEntity<Dictionary<string, object>>(
            // 指定中间表名
            "CategoryChildren",
            // 配置子类别到中间表的关联：ChildCategoryId对应ProductCategory.Id
            j => j.HasOne<ProductCategory>().WithMany().HasForeignKey("ChildCategoryId").HasPrincipalKey(p => p.Id),
            // 配置父类别到中间表的关联：Key对应ProductCategory.CategoryChildrenKey
            j => j.HasOne<ProductCategory>().WithMany().HasForeignKey("Key").HasPrincipalKey(p => p.CategoryChildrenKey),
            // 配置中间表主键
            j =>
            {
                j.Property<int>("Id");
                j.HasKey("Id");
            });
}

二、实现所需查询 #

1. 获取某类别的父类别 #

方式1：利用导航属性（推荐）

通过预先加载直接获取：

var targetId = 123; // 目标类别ID
var category = await _dbContext.ProductCategories
    .Include(p => p.Parent)
    .FirstOrDefaultAsync(p => p.Id == targetId);
var parentCategory = category?.Parent;

方式2：对应补充SQL的Linq写法

var targetId = 123;
var parentId = await _dbContext.ProductCategories
    .Where(child => child.Id == targetId)
    .Join(_dbContext.Set<Dictionary<string, object>>("CategoryChildren"),
        child => child.Id,
        cc => cc["ChildCategoryId"],
        (child, cc) => cc["Key"])
    .Join(_dbContext.ProductCategories,
        key => key,
        parent => parent.CategoryChildrenKey,
        (key, parent) => parent.Id)
    .FirstOrDefaultAsync();

2. 获取某类别的完整路径 #

使用SQL CTE实现递归查询，效率更高：

var targetId = 123;
var fullPath = await _dbContext.ProductCategories
    .FromSqlRaw($@"
        WITH CategoryPath AS (
            SELECT Id, Name, CategoryChildrenKey, CAST(Name AS VARCHAR(MAX)) AS Path
            FROM ProductCategory
            WHERE Id = {targetId}
            UNION ALL
            SELECT pc.Id, pc.Name, pc.CategoryChildrenKey, CONCAT(pc.Name, ' - ', cp.Path)
            FROM ProductCategory pc
            JOIN CategoryChildren cc ON pc.CategoryChildrenKey = cc.Key
            JOIN CategoryPath cp ON cc.ChildCategoryId = cp.Id
        )
        SELECT Path FROM CategoryPath
        WHERE CategoryChildrenKey NOT IN (SELECT Key FROM CategoryChildren)")
    .Select(p => p.Name) // 实际返回Path字段，可按需调整为匿名类或DTO
    .FirstOrDefaultAsync();

3. 获取某类别的所有直接子类别 #

方式1：利用导航属性

var targetId = 123;
var category = await _dbContext.ProductCategories
    .Include(p => p.Children)
    .FirstOrDefaultAsync(p => p.Id == targetId);
var directChildren = category?.Children;

方式2：Linq直接查询

var targetId = 123;
var targetKey = await _dbContext.ProductCategories
    .Where(p => p.Id == targetId)
    .Select(p => p.CategoryChildrenKey)
    .FirstOrDefaultAsync();

var directChildren = await _dbContext.ProductCategories
    .Where(child => _dbContext.Set<Dictionary<string, object>>("CategoryChildren")
        .Any(cc => cc["Key"].Equals(targetKey) && cc["ChildCategoryId"].Equals(child.Id)))
    .ToListAsync();

4. 获取从根节点开始的完整类别结构 #

先通过CTE查询所有层级数据，再组装成树形结构：

// 查询所有层级的类别数据
var allCategories = await _dbContext.ProductCategories
    .FromSqlRaw(@"
        WITH RecursiveCategories AS (
            SELECT Id, Name, CategoryChildrenKey, CAST(Id AS VARCHAR(MAX)) AS HierarchyPath, 0 AS Level
            FROM ProductCategory
            WHERE CategoryChildrenKey NOT IN (SELECT Key FROM CategoryChildren)
            UNION ALL
            SELECT pc.Id, pc.Name, pc.CategoryChildrenKey, CONCAT(rc.HierarchyPath, '.', pc.Id), rc.Level + 1
            FROM ProductCategory pc
            JOIN CategoryChildren cc ON pc.CategoryChildrenKey = cc.Key
            JOIN RecursiveCategories rc ON cc.ChildCategoryId = rc.Id
        )
        SELECT * FROM RecursiveCategories
        ORDER BY HierarchyPath")
    .ToListAsync();

// 组装成树形结构
var categoryDict = allCategories.ToDictionary(c => c.Id);
foreach (var category in allCategories.Where(c => c.Level > 0))
{
    var parentId = int.Parse(category.HierarchyPath.Split('.')[^2]);
    categoryDict[parentId].Children.Add(category);
}
var rootCategories = allCategories.Where(c => c.Level == 0).ToList();

三、限制递归深度（仅获取1层上下级） #

无需使用递归，直接加载一级导航即可：

// 获取带父类别的目标类别（仅1层）
var categoryWithParent = await _dbContext.ProductCategories
    .Include(p => p.Parent)
    .FirstOrDefaultAsync(p => p.Id == targetId);

// 获取带子类别的目标类别（仅1层）
var categoryWithChildren = await _dbContext.ProductCategories
    .Include(p => p.Children)
    .FirstOrDefaultAsync(p => p.Id == targetId);

内容的提问来源于stack exchange，提问作者wekso