score:0

I think the best way to achieve this without over calculation, multiple requests or temporary sql tables is select all table at once in a Dictionary and calculate parent count on C# side.

If it's acceptable for you it could be done using this function and addition class to prevent excessive calculation:

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;
}

Then you can get result using this code:

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();

score:0

I suggest it's better to change your table structure and add another field for pattern Database ER

Now you can find a deep level by blow code in high performance without recursive function

using (var context = new Entities())
            {
                var foundNode = context.Trees.FirstOrDefault(w => w.Id == 1);
                if (foundNode != null)
                {
                    var deepNode = context.Trees.Where(w => w.Id != foundNode.Id && w.Pattern.StartsWith(foundNode.Pattern)).Select(s => new { s.Pattern.Length, s })
                        .GroupBy(g => g.Length).OrderByDescending(o => o.Key).Select(s => s.Select(sm => sm.s)).FirstOrDefault();
                }
            }

Related Articles