2

I have a complex hierarchy and I have used RecursiveJoin Extension method and it returned to me a tree structure with parents nodes, children nodes and depth.So it's ok but I need to calculate total score point from ScoreItem table from database. As well as I had wrote this simple calculation method in t-sql at below,

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 

Also this my back end code for try to implement to linq query from above t-sql queries.

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

As a result these above codes return me, this result,

So I would like to return Json output for my web api,such as these

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

How can I implement hierarchy query with my other linq queries and return above Json format. if you have any suggestion and sample application about it, please may you share with me,

0

1 Answer 1

1

The RecursiveJoin Extension looks pretty nice, but I don't think you need the full power of that code to accomplish your objective. If I understand your question correctly, you need to enumerate over all lists of nodes from root to leaf, dumping each list into a JSON object. To do that, first create the following extension methods:

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

Then use them as follows:

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

This produces the JSON output:

{
  "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"
    }
  ]
}

Is this what you want? Here I am taking advantage of the fact that Json.NET serializes a dictionary as a JSON object to dynamically create the "levelI" properties as dictionary keys. Also, as you did not define the "val" value, I didn't include it in the dictionary.

Sign up to request clarification or add additional context in comments.

3 Comments

val, means, total point it's come from ScoreItem on DbContext and it has to show end of last level,
@Ozkan - there's nothing corresponding to ScoreItem on your FlatData. Can the data be added there?
actually I have to make join with Score, ScoreItem, Salespoint, and SalesHierarchy for prepare to calculate Score Card TotalPoint for each SalesPoint, for example, Alamut Kırtasiye but Who is the Alamut Kırtasiye, who is the its parents?? So that firstly I have to get hierarchy that involved with Alamut K. so "level1": "Anadolu Satış Merkezi", "level2": "Emrullah Çelik", / "level3": "Ahmet İşler" / "level4:" "Alamut K" "Val:" " 45.66", Val is showing final TotalPoint that calculated for instance, please take a look link here,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.