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:

{
Country: "UK",
City: "London",
Population: 8800000
}, {
Country: "UK",
City: "Edinburgh",
Population: 495400
}, {
Country: "France",
City: "Paris",
Population: 2244000
}

What we want to create is a structure like this:

{
Country: "UK",
Cities: [
  {
    City: "London",
    Population: 8800000
  }, {
    City: "Edinburgh",
    Population: 495400
  }]  
}, {
Country: "France",
Cities: [
  {
    City: "Paris",
    Population: 2244000
 }]
}

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.

public class Country {
  public string Country { get; set; }
  public List<City> Cities{ get; set; }
}

public class City {
  public string City { get; set; }
  public int Population { get; set; }
}

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

// flatData contains our flat data
var groupedByCountry = flatData.ToList()
 .GroupBy(x => new { x.Country })
 .Select(y => new Country() { 
     Country = y.Key.Country, 
     Cities = y.Select(c => new City() { 
          City = c.City, 
          Population = c.Population }).ToList() 
 });