Converting flattened hierarchical data from SQL Server into a structured JSON object with C#/Linq

Question

I am developing an MVC app that retrieves data from a table in SQL Server that is structured like so:

+-----------------------------------+
| Id | Name   | Hierarchy   | Depth |
|-----------------------------------|
| 01 | Justin | /           |     0 |
| 02 | Chris  | /1          |     1 |
| 03 | Beth   | /1/1        |     2 |
+-----------------------------------+

The example data in the Hierarchy column is the string representation of the hierarchyid datatype, and the Depth column is computed using the hierarchyid::GetLevel() method.

Using Entity Framework 4.1, I have mapped the above table to this class:

public class Node {
    public int Id { get; set; }
    public string Name { get; set; }
    public string HierarchyPath { get; set; } // String representation of the hierarchyid
    public int Depth { get; set; }
}

I want to use this information to display a graphical representation of the hierarchy to the user using the JS Visualizations Toolkit, which requires the data to be structured:

var node = {
    id: 1,
    name: 'Justin'
    children: [{
        id: 2,
        name: 'Chris',
        children: [{
            id: 3,
            name: 'Beth',
            children: []
        }]
    }]
}

I'm having trouble developing the logic to convert a list of my models into a structured JSON object. Any suggestions?

Answer 1

I know this question is old but I found myself with this problem and did not find the exact solution anywhere. I was able to build on @Jon's code and create the JSON like the OP posted by converting into POCO first. Posting here in case it helps someone.

The pre-requisite of this solution is that you know the maximum number of levels/depth your data has then you could do something like this:

  var nodesList = context.Nodes.OrderBy(x => x.Depth).ToList();
            var hierarchalData = new HierarchicalNode();
            foreach (var node in nodesList)
            {
                if (node.Depth == 1)
                {
                    // create the parent node
                    hierarchalData = new HierarchicalNode(node.Name, node.Id);
                }
                else
                {
                    // create the child node object
                    var childNode = new HierarchicalNode(node.Name, node.Id);

                    // split the hierarchy into an array to get parent indexes
                    var splitHierarchy = node.HierarchyPath.Split("/").Skip(1).SkipLast(1).ToArray();
                    switch (node.Depth)
                    {
                        case 2:
                            hierarchalData.Children.Add(childNode);
                            break;
                        case 3:
                            var lastParentIndex = Convert.ToInt32(splitHierarchy[splitHierarchy.Length - 2]) - 1;
                            hierarchalData.Children[lastParentIndex].Children.Add(childNode);
                            break;
                        case 4:
                            var firstParentIndex = Convert.ToInt32(splitHierarchy[splitHierarchy.Length - 3]) - 1;
                            var lastParentIndex1 = Convert.ToInt32(splitHierarchy[splitHierarchy.Length - 2]) - 1;
                            hierarchalData.Children[firstParentIndex].Children[lastParentIndex1].Children.Add(childNode);
                            break;
                        default:
                            break;
                    }
                }
            }

I know this approach is probably brute force but did the job for me. Number of levels in my case was 7.