Tag: Linq
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   });

LINQ to SQL Inserts and Deletes

Inserting and Deleting records in a database using LINQ to SQL is just as easy as selecting information. What's not so easy is actually finding out how to do it. There are lots of excellent blog posts around such as this one by Scott Guthrie http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx, however most of them we're all written for the Beta version of LINQ to SQL which let you do a .Add() or .Remove() on your table, which was  changed on the final release. 

So to insert do something like this: 

1DataClassesDataContext dataContext = new DataClassesDataContext();&nbsp;
2 
3//Create my new Movie record
4Movie movie = new Movie();
5movie.Name = "Tim's movie";&nbsp;
6 
7//Insert the movie into the data context
8dataContext.Movies.InsertOnSubmit(movie);&nbsp;
9 
10//Submit the change to the database
11dataContext.SubmitChanges();

And to delete do something like this:

1DataClassesDataContext dataContext = new DataClassesDataContext();
2 
3var movies = from m in dataContext.Movies
4                  where m.Name == "Tim's movie"
5                  select m;
6 
7dataContext.Movies.DeleteAllOnSubmit(movies);
8 
9dataContext.SubmitChanges();

LINQ to SQL Connection Strings

LINQ to SQL is great but like all great things at some point it does something that you don't expect and gives you a headache. An example of this happened to me this week with the differences between how connection strings are handled when you LINQ to SQL model is in a class library rather than a Website or Web Application.

What makes this issue particularly annoying is that it only appears when you try and change the database server that your code is looking at which could end up being when it's going live or moving to a staging server.

So we all know about connection strings, their quite simple and you just store them in your web.config file, which is how LINQ to SQL works when your using them in a Website. But as soon as you move them to a class library things change. First your connection string name is no longer that simple name you gave it e.g. ConnectionString, now it is prefixed with the namespace which is annoying but not the end of the world. Second discovery though is no matter what you do, it just doesn't seem to pick up the connection string from the web.config file. Reason being your original connection string has now compiled itself in the class library's dll and that is what it is using.

The Solution

Depending when you discovered this the solution is not to bad as you either have a lot of code to change or only a small amount. You can always pass a connection string to the constructor when you are creating an instance of the data context e.g.

1DataClasses1DataContext da = new DataClasses1DataContext(connectionstring);

You can also set the connection string on your LINQ to SQL model to be blank, this will remove the default constructor and force you to pass a connection string. This way you web application has the choice of what connection string to use and you can keep re-using your class library in different projects.