Build JSON Hierarchy from Structured Data

Question

C# | .NET 4.5 | Entity Framework 5

I have data coming back from a SQL Query in the form of ID,ParentID,Name. I'd like to take that data and parse it into a Hierarchical JSON string. So far it seems to be much more of a daunting task than it should be. Since I'm using Entity the data comes back nicely to me as an IEnumerable. Now I believe I just need some form of recursion, but I'm not quite sure where to start. Any help is appreciated.

Data Returns as

id   parentId    name
1    1           TopLoc
2    1           Loc1
3    1           Loc2
4    2           Loc1A

Code is

public static string GetJsonLocationHierarchy(long locationID)
{
    using (EntitiesSettings context = new EntitiesSettings())
    {
        // IEnumerable of ID,ParentID,Name
        context.GetLocationHierarchy(locationID);
    }
}

The end result I'd hope would be something like this:

{
    "id": "1",
    "parentId": "1",
    "name": "TopLoc",
    "children": [
        {
            "id": "2",
            "parentId": "1",
            "name": "Loc1",
            "children": [
                {
                    "id": "4",
                    "parentId": "2",
                    "name": "Loc1A",
                    "children": [
                        {}
                    ]
                }
            ]
        },
        {
            "id": "3",
            "parentId": "1",
            "name": "Loc2",
            "children": [
                {}
            ]
        }
    ]
}

Solution

One way to turn a flat table into a hierarchy is to put all your nodes into a dictionary. Then iterate over the dictionary, and for each node, look up its parent and add it to the parent's children. From there, you just need to find the root and serialize it.

Here is an example program to demonstrate the approach:

class Program
{
    static void Main(string[] args)
    {
        IEnumerable<Location> locations = new List<Location>
        {
            new Location { Id = 1, ParentId = 1, Name = "TopLoc" },
            new Location { Id = 2, ParentId = 1, Name = "Loc1" },
            new Location { Id = 3, ParentId = 1, Name = "Loc2" },
            new Location { Id = 4, ParentId = 2, Name = "Loc1A" },
        };

        Dictionary<int, Location> dict = locations.ToDictionary(loc => loc.Id);

        foreach (Location loc in dict.Values)
        {
            if (loc.ParentId != loc.Id)
            {
                Location parent = dict[loc.ParentId];
                parent.Children.Add(loc);
            }
        }

        Location root = dict.Values.First(loc => loc.ParentId == loc.Id);

        JsonSerializerSettings settings = new JsonSerializerSettings
        {
            ContractResolver = new CamelCasePropertyNamesContractResolver(),
            Formatting = Formatting.Indented
        };
        string json = JsonConvert.SerializeObject(root, settings);

        Console.WriteLine(json);
    }
}

class Location
{
    public Location()
    {
        Children = new List<Location>();
    }

    public int Id { get; set; }
    public int ParentId { get; set; }
    public string Name { get; set; }
    public List<Location> Children { get; set; }
}

Here is the output:

{
  "id": 1,
  "parentId": 1,
  "name": "TopLoc",
  "children": [
    {
      "id": 2,
      "parentId": 1,
      "name": "Loc1",
      "children": [
        {
          "id": 4,
          "parentId": 2,
          "name": "Loc1A",
          "children": []
        }
      ]
    },
    {
      "id": 3,
      "parentId": 1,
      "name": "Loc2",
      "children": []
    }
  ]
}

Comments

[Rod]

What's the best way to deserialize it?

[Brian Rogers]

@Rod To deserialize you just need to do Location loc = JsonConvert.DeserializeObject<Location>(json);

[Rod]

Thanks very much : )

[Rod]

do you have any ideas on how to take a hierarchy (either json or poco) and flatten to database? I'm trying to figure out how to take a new hierarchical object and insert (as a new record) into database.