Find depth level in parent-child hierarchy

Question

I have a table structure as shown below.

Id  |ParentId|  Name
--- |--------|-------
1   |NULL    |A
2   |1       |B
3   |2       |C
4   |3       |D

A is the parent of B, B is the parent of C, and C is the parent of D.

I want to calculate how parents of each record can? For example, B refers to A, C refers to B, and D refers to C.

In this case, the depth level for A is 0, B is 1, C is 2, and D is 3, depending on the number of parents they have.

I can do this using a recursive function, each time asking if there is any parent in the record. I want to achieve this with linq query in an efficient way.

Answer 1

, , sql - Dictionary #.

, , , :

public class ParentInfo
{
    public int? ParentId { get; }

    public int? ParentCount { get; set; }

    public ParentInfo(int? parentId)
    {
        ParentId = parentId;
    }
}

private static int GetParentCount(int id, IDictionary<int, ParentInfo> conections)
{
    if (!conections.ContainsKey(id))
        throw new InvalidDataException($"Id = {id} not found in connections");
    var info = conections[id];
    if (info.ParentCount.HasValue) return info.ParentCount.Value;

    var result = 0;
    if (info.ParentId.HasValue) result += 1 + GetParentCount(info.ParentId.Value, conections);
    info.ParentCount = result;
    return result;
}

:

var conections = table.ToDictionary(r => r.Id, r => new ParentInfo(r.ParentId));
var result = conections.Select(c => new
{
    Id = c.Key,
    ParentCount = GetParentCount(c.Key, conections)
}).ToArray();