EF Core中无限层级自连接表的名称过滤及父级查询需求

需求描述 #

对无限层级的自连接分类表，按Name属性执行过滤查询：

• 获取所有Name匹配的行
• 若匹配行是子节点（ParentId不为null），需递归获取其所有父节点，直至根节点（ParentId为null的节点）

实体类定义 #

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; } // 注：原定义中ParentId为非空int，但示例数据存在null值，建议改为可空类型避免异常
}

示例数据 #

解决方案 #

1. SQL递归查询（CTE） #

使用公共表表达式（CTE）实现递归逻辑，先筛选匹配节点，再向上追溯所有父节点：

WITH MatchingNodes AS (
    -- 第一步：筛选所有Name匹配的节点
    SELECT Id, ParentId, Name
    FROM Category
    WHERE Name = 'test' -- 替换为你的目标过滤值
),
RecursiveParents AS (
    -- 初始数据集：已匹配的节点
    SELECT Id, ParentId, Name
    FROM MatchingNodes
    UNION ALL
    -- 递归步骤：向上查找父节点
    SELECT c.Id, c.ParentId, c.Name
    FROM Category c
    INNER JOIN RecursiveParents rp ON c.Id = rp.ParentId
)
-- 去重后返回结果（避免多子节点共享父节点导致重复）
SELECT DISTINCT Id, ParentId, Name
FROM RecursiveParents
ORDER BY Id;

针对示例数据，若过滤Name='test'，最终结果会包含：匹配节点1、3、7，以及它们的父节点2（已自动去重根节点1）。

2. EF Core实现 #

方式一：直接调用SQL CTE

通过FromSqlRaw执行上述CTE查询，适合大数据量场景：

var targetName = "test";
var result = context.Categories
    .FromSqlRaw($@"
        WITH MatchingNodes AS (
            SELECT Id, ParentId, Name
            FROM Category
            WHERE Name = '{targetName}'
        ),
        RecursiveParents AS (
            SELECT Id, ParentId, Name
            FROM MatchingNodes
            UNION ALL
            SELECT c.Id, c.ParentId, c.Name
            FROM Category c
            INNER JOIN RecursiveParents rp ON c.Id = rp.ParentId
        )
        SELECT DISTINCT Id, ParentId, Name
        FROM RecursiveParents
        ORDER BY Id;
    ")
    .AsEnumerable();

方式二：内存递归处理（小数据量适用）

先加载全量数据，再在内存中递归收集父节点：

var targetName = "test";
var allCategories = await context.Categories.ToListAsync();

// 筛选所有匹配节点
var matchingNodes = allCategories.Where(c => c.Name == targetName).ToList();

// 递归获取父节点的方法
void CollectParents(Category node, List<Category> result)
{
    if (node.ParentId == null) return;
    var parent = allCategories.FirstOrDefault(c => c.Id == node.ParentId);
    if (parent != null && !result.Any(r => r.Id == parent.Id))
    {
        result.Add(parent);
        CollectParents(parent, result);
    }
}

// 收集匹配节点+所有父节点
var finalResult = new List<Category>(matchingNodes);
foreach (var node in matchingNodes)
{
    CollectParents(node, finalResult);
}

// 去重并排序
finalResult = finalResult.DistinctBy(c => c.Id).OrderBy(c => c.Id).ToList();

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