recursive query using dapper in .net core-CodePudding

I have a table(Campaign) which has self-referencing, the design looks like below;

   public class Campaign: SqlEntityBase
    {
        public int ParentCampaignId { get; set; }
        public string Name { get; set; }
        public Campaign ParentCampaign { get; set; }
    }
// ParentCampaignId is FK to itself

Now I need to retrieve a Campaign including its child,

I am trying to retrieve entity with Id=6,

Code below works fine for first child(4), but couldn't retrieve child of it(2).

There might be even more nested entities how can I retrieve all child entities...

Class:

 public async Task<Campaign> GetAsync(int id)
        {
            using var dbConnection = _context.CreateConnection();
            string query = @"SELECT d.[Id], d.[ParentCampaignId], d.[Name] FROM [dbo].[Campaign] d 
                             join [dbo].[Campaign] dc on 
                             d.ParentCampaignId = dc.Id 
                             WHERE d.[Id] = @Id";
                var users = await dbConnection.QueryAsync<Campaign, Campaign, Campaign>(query, (p, camp) =>
                {
                    p.ParentCampaign = camp;
                    return p;
                }, splitOn: "Id,ParentCampaignId", param: new { Id = id });
                return users.FirstOrDefault();
            }

CodePudding user response：

Just doing multi-mapping isn't going to work here, because you want to get recursive results.

You can use a recursive CTE query to get all parent rows. You then have two options to map them in:

You can either map them using a Dictionary, by loading them in first, then iterating through them again and assigning the parent object.
Or you can ensure the recursion returns in the correct order (by using ORDER BY) and just keeping track of the last one.

public class Campaign : SqlEntityBase
{
    public int Id { get; set; }
    public int ParentCampaignId { get; set; }
    public string Name { get; set; }
    public Campaign ParentCampaign { get; set; }
}

public async Task<Campaign> GetAsync(int id)
{
    using var dbConnection = _context.CreateConnection();
    string query = @"
WITH cte AS (
    SELECT d.Id, d.ParentCampaignId, d.Name, 0 AS lvl
      FROM dbo.Campaign c
      WHERE c.Id = @Id

    UNION ALL

    SELECT c.Id, c.ParentCampaignId, c.Name, cte.lvl   1
      FROM dbo.Campaign c
      JOIN cte on cte.ParentCampaignId = c.Id 
)
SELECT d.Id, d.ParentCampaignId, d.Name
FROM dbo.Campaign d 
ORDER BY lvl;
";
    Campaign first = null;
    Campaign previous = null;
    foreach (var user in await dbConnection.QueryAsync<Campaign>(query))
    {
        if (first == null)
        {
            first = campaign;
            previous = campaign;
        }
        else
        {
            previous.Parent = campaign;
            previous = campaign;
        }
    }
    return first;
}