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

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?

EDIT: I don't have time to fix the answer below right now, but given the extra information in the question, I suspect you want to keep a Dictionary<int, HierarchicalNode> rather than a List<HierarchicalNode> so that you're not relying on any ordering...

I would forget about the JSON representation to start with, and concentrate on building an in-memory POCO representation of the hierarchy. To do that, I'd use something like this:

class HierarchicalNode
{
    private readonly List<HierarchicalNode> children =
        new List<HierarchicalNode>();        
    public List<HierarchicalNode> Children { get { return children; } }

    private readonly string name;
    public string Name { get { return name; } }

    private readonly int id;
    public int Id { get { return id; } }

    public HierarchicalNode(string name, int id)
    {
        this.name = name;
        this.id = id;
    }
}

Then build up the tree like this:

// Make sure we get everything in a sensible order, parents before children
var query = context.Nodes.OrderBy(x => x.Depth);

var root = new HierarchicalNode("Root", 0);
foreach (var node in query)
{       
    var current = root;
    foreach (string part = node.HierarchyPath.Split(new[] {'/'},
                                   StringSplitOptions.RemoveEmptyEntries))
    {
        int parsedPart = int.Parse(part);
        current = current.Children[parsedPart - 1];
    }
    current.Children.Add(new HierarchicalNode(node.Name, node.Id));
}

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.