获取全层级所有节点父节点的更优实现方案咨询

问题现状 #

当前通过循环异步查询获取全层级父节点、修改关联实体属性后保存的方案虽能正常运行，但在树结构深度较大时会触发大量SQL查询（N+1问题），导致性能下降。原代码如下：

while (parent != null)
{
    parents.Add(parent);

    parent = await _dbContext.Team
                             .Where(x => x.Id == parent.ParentId)
                             .Include(x => x.Customer)
                             .ThenInclude(x => x.User)
                             .FirstOrDefaultAsync();
}

foreach (var entity in entities)
{
    entity.Name = "New Name";
    entity.Customer.Name = "New Customer Name";
    entity.Customer.User.Name = "New User Name";
}

await _dbContext.SaveChangesAsync();

优化方案 #

1. 递归CTE一次性查询所有父节点 #

利用SQL的递归公共表表达式（CTE），仅需一次SQL查询即可获取指定节点的全层级父节点，同时预加载关联的Customer和User数据，彻底解决N+1查询问题。

示例代码：

// 假设初始节点为currentTeam
var currentTeamId = currentTeam.Id;

// 通过递归CTE查询所有父节点及关联数据
var parents = await _dbContext.Team
    .FromSqlRaw(@"
        WITH RecursiveParentTeams AS (
            -- 起始节点：当前节点
            SELECT Id, ParentId, Name, CustomerId
            FROM Teams
            WHERE Id = {0}
            UNION ALL
            -- 递归向上查询父节点
            SELECT t.Id, t.ParentId, t.Name, t.CustomerId
            FROM Teams t
            JOIN RecursiveParentTeams rpt ON t.Id = rpt.ParentId
        )
        SELECT * FROM RecursiveParentTeams", currentTeamId)
    .Include(t => t.Customer)
        .ThenInclude(c => c.User)
    .ToListAsync();

2. 预存父节点路径（适合频繁查询场景） #

在Team表中新增ParentPath字段（如VARCHAR(MAX)类型），存储节点的全层级父ID路径（格式建议为",1,3,5,"，避免部分匹配）。每次修改节点的父级关系时，同步更新该字段的路径值。

查询时可直接通过路径匹配获取所有父节点：

var currentTeamId = currentTeam.Id;
var parents = await _dbContext.Team
    .Where(t => t.ParentPath.Contains($",{currentTeamId},"))
    .Include(t => t.Customer)
        .ThenInclude(c => c.User)
    .ToListAsync();

3. 批量更新替代内存循环修改 #

若需修改的实体数量较多，无需将所有实体加载到内存后逐个修改，直接通过原生SQL执行批量更新，大幅提升性能：

// 批量更新Team的Name
var teamIds = entities.Select(e => e.Id).Distinct();
await _dbContext.Database.ExecuteSqlRawAsync(
    "UPDATE Teams SET Name = 'New Name' WHERE Id IN ({0})",
    string.Join(",", teamIds));

// 批量更新关联Customer的Name
var customerIds = entities.Select(e => e.Customer.Id).Distinct();
await _dbContext.Database.ExecuteSqlRawAsync(
    "UPDATE Customers SET Name = 'New Customer Name' WHERE Id IN ({0})",
    string.Join(",", customerIds));

// 批量更新关联User的Name
var userIds = entities.Select(e => e.Customer.User.Id).Distinct();
await _dbContext.Database.ExecuteSqlRawAsync(
    "UPDATE Users SET Name = 'New User Name' WHERE Id IN ({0})",
    string.Join(",", userIds));

注意：使用原生SQL批量更新时，需确保权限允许，且数据校验逻辑已提前处理。

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