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()
});