.NET Core中使用Dapper实现自关联Campaign表全层级递归查询

核心思路 #

处理不确定深度的自关联层级数据，SQL Server 端用**递归CTE（公用表表达式）**一次性查询出目标节点 + 所有后代节点，再在内存中组装父子导航关系即可，避免多次数据库查询或单层Join的局限性。

前置调整 #

首先在Campaign实体类中增加子节点集合导航属性，方便层级嵌套存储：

public class Campaign: SqlEntityBase
{
    public int ParentCampaignId { get; set; }
    public string Name { get; set; }
    public Campaign ParentCampaign { get; set; }
    // 新增子节点集合
    public List<Campaign> ChildCampaigns { get; set; } = new List<Campaign>();
}

实现代码 #

public async Task<Campaign> GetAsync(int id)
{
    using var dbConnection = _context.CreateConnection();
    // 递归CTE查询目标节点+所有层级子节点
    string query = @"
WITH RecursiveCampaigns AS
(
    -- 锚点成员：查询指定ID的根节点
    SELECT Id, ParentCampaignId, Name 
    FROM [dbo].[Campaign] 
    WHERE Id = @Id
    UNION ALL
    -- 递归成员：迭代查询所有后代子节点
    SELECT c.Id, c.ParentCampaignId, c.Name 
    FROM [dbo].[Campaign] c
    INNER JOIN RecursiveCampaigns rc ON c.ParentCampaignId = rc.Id
)
SELECT Id, ParentCampaignId, Name FROM RecursiveCampaigns";
    // 一次性查询出所有相关节点
    var allCampaigns = (await dbConnection.QueryAsync<Campaign>(query, new { Id = id })).ToList();
    if (!allCampaigns.Any()) return null;
    // 内存中组装父子关系
    var campaignLookup = allCampaigns.ToDictionary(c => c.Id);
    foreach (var campaign in allCampaigns.Where(c => c.ParentCampaignId != 0)) 
    {
        if (campaignLookup.TryGetValue(campaign.ParentCampaignId, out var parent))
        {
            parent.ChildCampaigns.Add(campaign);
            campaign.ParentCampaign = parent;
        }
    }
    // 返回根节点（包含所有层级子节点）
    return campaignLookup[id];
}

备注：如果你的表中根节点的ParentCampaignId是可空int?类型，只需要把组装关系时的过滤条件改为c.ParentCampaignId.HasValue即可。

原代码问题说明 #

1. 查询方向错误：原SQL通过d.ParentCampaignId = dc.Id关联，实际查询的是指定ID节点的父节点，和你需要的子节点方向相反
2. 单层Join只能处理一级关联，无法适配不确定深度的多层级嵌套场景

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