在EF Core中递归访问自引用树形模型的关联Profile项

针对百万级层级结构的查询需求，必须依赖**数据库端的递归公共表表达式(CTE)**实现，避免客户端递归带来的性能灾难。以下是两种适配EF Core的实现方式：

方法一：EF Core LINQ递归查询（EF Core 5.0+） #

通过LINQ结合原生SQL CTE，直接在数据库中完成递归遍历，覆盖三类数据：

1. 自身关联指定Profile的节点
2. 上述节点的所有后代子项
3. 父链中存在指定Profile节点的所有子项

假设targetProfileIds是你需要匹配的Profile ID集合：

var targetProfileIds = new List<int> { /* 填入目标Profile ID */ };

// 构造递归CTE查询，获取所有符合条件的Item ID
var validItemIds = _context.Items
    .FromSqlRaw(@"
        WITH RecursiveItems AS (
            -- 初始节点：所有直接关联指定Profile的项
            SELECT Id FROM Items WHERE ProfileId IN ({0})
            UNION ALL
            -- 递归获取所有后代子项
            SELECT i.Id FROM Items i
            INNER JOIN RecursiveItems ri ON i.ParentId = ri.Id
            UNION ALL
            -- 递归获取所有父链含指定Profile的子项
            SELECT i.Id FROM Items i
            INNER JOIN RecursiveItems ri ON i.Id = ri.ParentId
        )
        SELECT DISTINCT Id FROM RecursiveItems
    ", string.Join(",", targetProfileIds))
    .Select(ri => ri.Id)
    .ToList();

// 根据ID集合获取完整的Item实体
var result = _context.Items
    .Where(i => validItemIds.Contains(i.Id))
    .Include(i => i.Profile) // 按需加载关联数据
    .Include(i => i.Children)
    .ToList();

方法二：纯原生SQL查询（性能最优） #

对于170万条数据的场景，纯原生SQL的CTE执行效率更高，直接映射到实体：

var targetProfileIds = new List<int> { /* 填入目标Profile ID */ };
var idList = string.Join(",", targetProfileIds);

var sql = @"
WITH RecursiveItems AS (
    SELECT Id FROM Items WHERE ProfileId IN ({0})
    UNION ALL
    SELECT i.Id FROM Items i JOIN RecursiveItems ri ON i.ParentId = ri.Id
    UNION ALL
    SELECT i.Id FROM Items i JOIN RecursiveItems ri ON i.Id = ri.ParentId
)
SELECT i.* FROM Items i
JOIN (SELECT DISTINCT Id FROM RecursiveItems) ri ON i.Id = ri.Id";

var result = _context.Items
    .FromSqlRaw(sql, idList)
    .Include(i => i.Profile)
    .Include(i => i.Children)
    .ToList();

关键优化点 #

• 给数据库表的ParentId、ProfileId列添加非聚集索引，这是百万级数据递归查询的性能基础。
• 确保ProfileId不为空的场景下，可添加NOT NULL约束进一步优化查询。
• 避免使用客户端递归遍历，所有逻辑必须在数据库端执行，否则会导致内存溢出或极端缓慢。

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