Turning flat data into a hierarchy using C#

Turning flat data into a hierarchy using C#

Sometimes you have flat data and what you really want is a hierarchy. This can often happen when data is stored in a relational database that you want to return as JSON in an API. One SQL call will return a flat structure, but as JSON can give a complete hierarchy it makes more sense to convert it.

Let's assume we have the following as our source data:

1{
2  Country: "UK",
3  City: "London",
4  Population: 8800000
5}, {
6  Country: "UK",
7  City: "Edinburgh",
8  Population: 495400
9}, {
10  Country: "France",
11  City: "Paris",
12  Population: 2244000
13}

What we want to create is a structure like this:

1{
2  Country: "UK",
3  Cities: [
4    {
5      City: "London",
6      Population: 8800000
7    }, {
8      City: "Edinburgh",
9      Population: 495400
10    }]  
11}, {
12  Country: "France",
13  Cities: [
14    {
15      City: "Paris",
16      Population: 2244000
17   }]
18}

To make the conversion of flat data to a hierarchy using C# we can use a LInq expression.

First I need two models to represent the final structure. One to represent the Country and the other to represent the City. The Country class contains a list of cities.

1public class Country {
2    public string Country { get; set; }
3    public List<City> Cities{ get; set; }
4}
5

6public class City {
7    public string City { get; set; }
8    public int Population { get; set; }
9}

The following Linq query will then create a list of Countries populating the City list by doing a sub-select on the original dataset.

1// flatData contains our flat data
2var groupedByCountry = flatData.ToList()
3   .GroupBy(x => new { x.Country })
4   .Select(y => new Country() { 
5       Country = y.Key.Country, 
6       Cities = y.Select(c => new City() { 
7            City = c.City, 
8            Population = c.Population }).ToList() 
9   });