c# - 在具有多个表的 Linq 查询中使用递归层次结构树结构并返回一些 Json 值

Question

我有一个复杂的层次结构，我使用了 RecursiveJoin Extension方法，它返回给我一个包含父节点、子节点和深度的树结构。所以没关系，但我需要从数据库的 ScoreItem 表中计算总分。以及我在下面的t-sql中写了这个简单的计算方法，

Begin
    Select 
        sp.Name,
        u.FirstName + ' ' + u.LastName as NameAndSurname, 
        rgc.Name as ScoreCard,
        si.TotalPoint 
    from Score s
        inner join ScoreItem si on si.ScoreId = s.Id
        inner join ProjectResearchGroup prg on si.ProjectResearchGroupId = prg.Id
        inner join RgClone rgc on prg.RgCloneId = rgc.Id
        inner join Salespoint sp on s.SalesPointId = sp.Id
        inner join SalesHierarchySalesPoint shsp on sp.Id = shsp.SalesPointId
        inner join SalesHierarchy sh on shsp.SalesHierarchyId = sh.Id
        inner join [User] u on u.Id = sh.CompanyResponsibleId
    where sh.Id in 
        (Select Id 
        from SalesHierarchy 
        where ParentId in 
            (Select Id 
            from SalesHierarchy 
            where ParentId in 
                (Select Id 
                from SalesHierarchy 
                where ParentId in 
                    (Select Id 
                    from SalesHierarchy 
                    where CompanyId = 2 
                        and ParentId is null))))
        and si.IsValidForSalesPoint = 1 
End 

这也是我的后端代码，用于尝试从上面的 t-sql 查询实现 linq 查询。

IEnumerable<FlatData> _SalesHierarchy = db.SalesHierarchy
                                         .Select(sh =>
                                         new FlatData()
                                         {
                                         Id = sh.Id,
                                         ParentId = sh.ParentId ?? 0,
                                         Text = sh.Name
                                         })
                                        .ToList();

IEnumerable<DeepNodeData> nodes = _SalesHierarchy.RecursiveJoin(e => e.Id, e => e.ParentId,
(FlatData sh, int index, int depth, IEnumerable<DeepNodeData> children) =>
  {
    return new DeepNodeData
        {
          Id = sh.Id,
          ParentId = sh.ParentId,
          Text = sh.Text,
          Children = children,
          Depth = depth
        };

结果这些上面的代码返回我，this result ,

所以我想为我的 web api 返回 Json 输出，比如这些

"data": [
        {
            "level1": "XXXX,XXXX,XXXX",
            "level2": "XXXX,XXXX,XXX",
            "level3": "XXXX,XXXX,XX",
            "level4": "XXXX,XXXX,X",
            "val": 2
        },
    ]
}; 

如何使用我的其他 linq 查询实现层次结构查询并返回以上 Json 格式。如果您对此有任何建议和示例应用，请与我分享，

Answer 1

RecursiveJoin Extension看起来很不错，但我认为您不需要该代码的全部功能来实现您的目标。如果我正确理解你的问题，你需要枚举从根到叶的所有节点列表，将每个列表转储到一个 JSON 对象中。为此，首先创建以下扩展方法:

public static class RecursiveExtensions
{
    public static IEnumerable<TValue> SelfAndParents<TKey, TValue>(this TValue value, IDictionary<TKey, TValue> dictionary, Func<TValue, TKey> getParentKey)
    {
        HashSet<TValue> returned = new HashSet<TValue>();
        do
        {
            yield return value;
            if (!dictionary.TryGetValue(getParentKey(value), out value))
                yield break;
            if (returned.Contains(value))
                throw new InvalidOperationException("Circular reference");
            returned.Add(value);
        }
        while (true);
    }

    public static IEnumerable<List<TValue>> FlattenTree<TKey, TValue>(this IEnumerable<TValue> nodes, Func<TValue, TKey> getKey, Func<TValue, TKey> getParentKey)
    {
        var list = nodes.ToList();                                                          // Don't iterate through the node list more than once.
        var parentKeys = new HashSet<TKey>(list.Select(getParentKey));                      // Built a set of parent keys.
        var dict = list.ToDictionary(getKey);                                               // Build a dictionary of key to value
        var results = list
            .Where(node => !parentKeys.Contains(getKey(node)))                              // Filter out non-leaf nodes
            .Select(node => node.SelfAndParents(dict, getParentKey).Reverse().ToList());    // For each leaf return a list going from root to leaf.
        return results;
    }
}

然后按如下方式使用它们:

public static class TestFlatten
{
    public static IEnumerable<FlatData> GetFlatData()
    {
        // Get sample data for testing purposes.
        var list = new List<FlatData>
        {
            new FlatData { Id = 1, ParentId = 0, Text = "Some Root Node" },
            new FlatData { Id = 2, ParentId = 0, Text = "Anadolu Satış Merkezi" },
            new FlatData { Id = 3, ParentId = 2, Text = "Emrullah Çelik" },
            new FlatData { Id = 4, ParentId = 3, Text = "Ahmet İşler" },
            new FlatData { Id = 5, ParentId = 4, Text = "Elpa Pazarlama Nazmi Appak" },
            new FlatData { Id = 6, ParentId = 4, Text = "Elpa Pazarlama Nazmi Appak Redux" },
            new FlatData { Id = 11, ParentId = 1, Text = "Some Child of Some Root Node" },
        };
        return list;
    }

    public static void Test()
    {
        var nodes = GetFlatData();
        var flatNodes = nodes.FlattenTree(d => d.Id, d => d.ParentId);
        var results = flatNodes.Select(
            list => list
                .Select((d, i) => new KeyValuePair<int, FlatData>(i, d))
                .ToDictionary(pair => string.Format("level{0}", pair.Key + 1), pair => pair.Value.Text))
            .ToList();
        var json = JsonConvert.SerializeObject(new { data = results }, Formatting.Indented);
        Debug.WriteLine(json);
    }
}

这会产生 JSON 输出:

{
  "data": [
    {
      "level1": "Anadolu Satış Merkezi",
      "level2": "Emrullah Çelik",
      "level3": "Ahmet İşler",
      "level4": "Elpa Pazarlama Nazmi Appak"
    },
    {
      "level1": "Anadolu Satış Merkezi",
      "level2": "Emrullah Çelik",
      "level3": "Ahmet İşler",
      "level4": "Elpa Pazarlama Nazmi Appak Redux"
    },
    {
      "level1": "Some Root Node",
      "level2": "Some Child of Some Root Node"
    }
  ]
}

这是你想要的吗？在这里，我利用 Json.NET 将字典序列化为 JSON 对象这一事实来动态创建“levelI”属性作为字典键。此外，由于您没有定义 "val" 值，因此我没有将其包含在字典中。