求针对组织层级树形表生成指定格式结果的LINQ查询语句

首先定义对应数据表的实体类：

public class OrgHierarchy
{
    public int Id_Org_hierarchy { get; set; }
    public string Hierarchy_Name { get; set; }
    public int Parent_Id_Org_hierarchy { get; set; }
}

准备示例数据集合：

var orgData = new List<OrgHierarchy>
{
    new OrgHierarchy { Id_Org_hierarchy = 1, Hierarchy_Name = "OGt", Parent_Id_Org_hierarchy = 0 },
    new OrgHierarchy { Id_Org_hierarchy = 2, Hierarchy_Name = "NHT", Parent_Id_Org_hierarchy = 1 },
    new OrgHierarchy { Id_Org_hierarchy = 3, Hierarchy_Name = "BATCH 1", Parent_Id_Org_hierarchy = 2 },
    new OrgHierarchy { Id_Org_hierarchy = 3, Hierarchy_Name = "Voice", Parent_Id_Org_hierarchy = 1 }
};

编写递归方法用于追溯父节点名称（按从直接父到根节点的顺序收集）：

private static List<string> GetParentNames(OrgHierarchy node, List<OrgHierarchy> allData)
{
    var parentNames = new List<string>();
    var parent = allData.FirstOrDefault(x => x.Id_Org_hierarchy == node.Parent_Id_Org_hierarchy);
    if (parent != null && parent.Id_Org_hierarchy != 0)
    {
        parentNames.Add(parent.Hierarchy_Name);
        parentNames.AddRange(GetParentNames(parent, allData));
    }
    return parentNames;
}

最后用LINQ查询生成目标格式的结果：

var result = orgData
    .Where(node => node.Parent_Id_Org_hierarchy != 0) // 过滤根节点
    .Select(node => 
    {
        var parentNames = GetParentNames(node, orgData);
        return $"{node.Hierarchy_Name}({string.Join(",", parentNames)})";
    })
    .ToList();

执行后result会输出以下内容：

• NHT(OGt)
• BATCH 1(NHT,OGt)
• Voice(OGt)

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