Turning flat data into a hierarchy using C#

13 January 2023

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

How to create a Graph QL API on Azure Functions

1 September 2022

REST APIs are great, but they can result in either your application making an excessive number of requests to multiple endpoints, then only using a small percentage of the data returned. Or you end up making a large number of endpoints for specific purposes and now have maintenance hell to deal with.

If that's your situation then one option is to look at replacing some of that functionality with a Graph QL API. I'm not going to dig into what Graph QL APIs are (that's been covered by many people before me), but what I will do is show you how to make one in an Azure Function.

Your starting point is to use Hot Chocolate by Chilli Cream, not only does it have a fun meaningless name, but it also offers some great simple-to-use functionality. However, despite stating it works with Azure Functions, the documentation is all for ASP.NET Core, which is not the same thing.

Another issue I have with the documentation is that it doesn't explain particularly well how you configure it to work with a data access layer. Examples either have methods that return a dataset containing all related data, or they use Entity Framework, which as you generally wouldn't use your DB schema as an API schema feels like cheating.

So here is my guide from file new project to a working Graph QL API in an Azure Function.

File New Project

Starting right at the beginning, open Visual Studio and create a new Azure Function. For this demo, I'm using .NET 6 as that's the latest at the time of writing, and am going to create an HTTP trigger.

For a data source, I've created a hard-coded repository containing Schools, Classes, and Students. Schools contain multiple classes and classes contain multiple students. Each repository contains functions to get all, get by id or get by the thing it's related to. e.g. Get Students by Class. Here's my code for it.

1using AzureFunctionWithGraphApi.Models;
2using System.Collections.Generic;
3using System.Linq;
4
5namespace AzureFunctionWithGraphApi.DataAccess
6{
7    public interface ISchoolRepository
8    {
9        List<School> All();
10        School GetById(int id);
11    }
12
13    public interface IClassRepository
14    {
15        List<Class> All();
16        Class GetById(int id);
17        List<Class> GetBySchool(int schoolId);
18    }
19
20    public interface IStudentRepository {
21        List<Student> All();
22        Student GetById(int id);
23        List<Student> GetByClass(int classId);
24    }
25
26    public static class DemoData
27    {
28        public static List<School> Schools = new List<School>()
29        {
30            new School() {Id = 1, Name = "Foo School"},
31            new School() {Id = 2 , Name = "Boo School"},
32        };
33
34        public static List<Class> ClassList = new List<Class>()
35        {
36            new Class() {Id = 3, SchoolId = 1, Name = "Red Class", YearGroup = 1},
37            new Class() {Id = 4, SchoolId = 1, Name = "Blue Class", YearGroup = 2},
38            new Class() {Id =5, SchoolId = 2, Name = "Yellow Class", YearGroup = 1},
39            new Class(){Id = 6, SchoolId = 2, Name = "Green Class", YearGroup = 2}
40        };
41
42        public static List<Student> Students = new List<Student>()
43        {
44            new Student() {Id = 1, ClassId = 3, FirstName = "John", Surname = "Smith"},
45            new Student() {Id = 2, ClassId = 3, FirstName = "Sam", Surname = "Smith"},
46            new Student() {Id = 3, ClassId = 4, FirstName = "Eric", Surname = "Smith"},
47            new Student() {Id = 4, ClassId = 4, FirstName = "Rachel", Surname = "Smith"},
48            new Student() {Id = 5, ClassId = 5, FirstName = "Tom", Surname = "Smith"},
49            new Student() {Id = 6, ClassId = 5, FirstName = "Sally", Surname = "Smith"},
50            new Student() {Id = 7, ClassId = 6, FirstName = "Sharon", Surname = "Smith"},
51            new Student() {Id = 8, ClassId = 6, FirstName = "Kate", Surname = "Smith"}
52        };
53    }
54
55    public class SchoolRepository : ISchoolRepository
56    {
57        public List<School> All()
58        {
59            return DemoData.Schools;
60        }
61
62        public School GetById(int id)
63        {
64            return DemoData.Schools.Where(x => x.Id == id).FirstOrDefault(); 
65        }
66    }
67
68    public class ClassRepository : IClassRepository
69    {
70        public List<Class> All()
71        {
72            return DemoData.ClassList;
73        }
74
75        public Class GetById(int id)
76        {
77            return DemoData.ClassList.Where(x => x.Id == id).FirstOrDefault();
78        }
79
80        public List<Class> GetBySchool(int schoolId)
81        {
82            return DemoData.ClassList.Where((x) => x.SchoolId == schoolId).ToList();
83        }
84    }
85
86    public class StudentRepository : IStudentRepository
87    {
88        public List<Student> All()
89        {
90            return DemoData.Students;
91        }
92
93        public List<Student> GetByClass(int classId)
94        {
95            return DemoData.Students.Where((x) => x.ClassId == classId).ToList();
96        }
97
98        public Student GetById(int id)
99        {
100            return DemoData.Students.Where(x => x.Id == id).FirstOrDefault();
101        }
102    }
103}
104

If you want to use it, you'll also need the related models.

1public class School
2    {
3        public int Id { get; set; }
4        public string Name { get; set; }
5    }
6
7public class Class
8    {
9        public int Id { get; set; }
10        public int SchoolId { get; set; }
11        public int YearGroup { get; set; }
12        public string Name { get; set; }
13    }
14
15public class Student
16    {
17        public int Id { get; set; }
18        public int ClassId { get; set; }
19        public string FirstName { get; set; }
20        public string Surname { get; set; }
21    }

Create a Graph QL API

With our project and data access layer created, lets get on with how to create a Graph QL in a .NET Azure Function.

Hot chocolate will provide all the functionality and can be added to your solution via Nuget. Just search for Hot Chocolate and make sure you pick the Azure Function version.

The HTTP Endpoint we created when creating the function needs updating to provide the route for the graph API.

1using System.Threading.Tasks;
2using Microsoft.AspNetCore.Mvc;
3using Microsoft.Azure.WebJobs;
4using Microsoft.Azure.WebJobs.Extensions.Http;
5using Microsoft.AspNetCore.Http;
6using Microsoft.Extensions.Logging;
7using HotChocolate.AzureFunctions;
8
9namespace AzureFunctionWithGraphApi
10{
11    public class GraphQlApi
12    {
13        [FunctionName("HttpExample")]
14        public async Task<IActionResult> Run(
15        [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = "graphql/{**slug}")] HttpRequest req,
16        [GraphQL] IGraphQLRequestExecutor executor,
17        ILogger log)
18        {
19            log.LogInformation("C# HTTP trigger function processed a request.");
20
21            return await executor.ExecuteAsync(req);
22        }
23    }
24}
25

Next we need to configure what queries can be performed on the graph. For my example, I'm replicating the Get All and Get By Id methods from my data access layer.

One thing to note here is although I'm using dependency injection for my repositories they are using resolver injection on the methods rather than constructor injection. You can read more about why this is on the Chilli Cream site here, but essentially constructor injector won't work.

1using AzureFunctionWithGraphApi.DataAccess;
2using AzureFunctionWithGraphApi.Models;
3using HotChocolate;
4using System.Collections.Generic;
5
6namespace AzureFunctionWithGraphApi
7{
8    public class Query
9    {
10        public List<School> GetSchools([Service] ISchoolRepository schoolRepository)
11        {
12            return schoolRepository.All();
13        }
14        public School GetSchoolById([Service] ISchoolRepository schoolRepository, int schoolId)
15        {
16            return schoolRepository.GetById(schoolId);
17        }
18
19        public List<Class> GetClasses([Service] IClassRepository classRepository)
20        {
21            return classRepository.All();
22        }
23        public Class GetClassById([Service] IClassRepository classRepository, int classId)
24        {
25            return classRepository.GetById(classId);
26        }
27
28        public List<Class> GetClassesBySchoolId([Service] IClassRepository classRepository, int schoolId)
29        {
30            return classRepository.GetBySchool(schoolId);
31        }
32
33        public List<Student> GetStudents([Service] IStudentRepository studentRepository)
34        {
35            return studentRepository.All();
36        }
37        public Student GetStudentById([Service] IStudentRepository studentRepository, int studentId)
38        {
39            return studentRepository.GetById(studentId);
40        }
41
42        public List<Student> GetStudentsBySchoolId([Service] IStudentRepository studentRepository, int classId)
43        {
44            return studentRepository.GetByClass(classId);
45        }
46    }
47}
48

At this point (apart from the fact we haven't configured the startup file with our DI) you will now have a Graph QL API but it won't be able to load any related items. You will however be able to pick which fields you want from the datasets.

To add the related data we need to create extension methods for our models. These inject the instance of the item using Hot Chocolates Parent attribute, and the repository we're going to use to get the data.

1using AzureFunctionWithGraphApi.DataAccess;
2using AzureFunctionWithGraphApi.Models;
3using HotChocolate;
4using HotChocolate.Types;
5using System.Collections.Generic;
6
7namespace AzureFunctionWithGraphApi
8{
9    [ExtendObjectType(typeof(School))]
10    public class SchoolExtensions
11    {
12        public List<Class> GetClasses([Parent] School school, [Service] IClassRepository classRepository)
13        {
14            return classRepository.GetBySchool(school.Id);
15        }
16    }
17
18    [ExtendObjectType(typeof(Class))]
19    public class ClassExtensions
20    {
21        public School GetSchool([Parent] Class schoolClass, [Service] ISchoolRepository schoolRepository)
22        {
23            return schoolRepository.GetById(schoolClass.SchoolId);
24        }
25        public List<Student> GetStudents([Parent] Class schoolClass, [Service] IStudentRepository studentRepository)
26        {
27            return studentRepository.GetByClass(schoolClass.Id);
28        }
29    }
30
31    [ExtendObjectType(typeof(Student))]
32    public class StudentExtensions
33    {
34        public Class GetClass([Parent] Student student, [Service] IClassRepository classRepository)
35        {
36            return classRepository.GetById(student.ClassId);
37        }
38    }
39}
40

Now all that's left is to configure our startup file. This file no longer gets created when you create the Azure Function so you'll need to add it yourself.

Here's mine. As you can see I'm registering the dependency injection for my repositories, and also configuring the GraphQL. This needs to include the query class we made and any extension classes.

1using AzureFunctionWithGraphApi.DataAccess;
2using Microsoft.Azure.Functions.Extensions.DependencyInjection;
3using Microsoft.Extensions.DependencyInjection;
4
5[assembly: FunctionsStartup(typeof(AzureFunctionWithGraphApi.Startup))]
6namespace AzureFunctionWithGraphApi
7{
8    public class Startup : FunctionsStartup
9    {
10        public override void Configure(IFunctionsHostBuilder builder)
11        {
12            builder.Services.AddScoped<ISchoolRepository, SchoolRepository>();
13            builder.Services.AddScoped<IClassRepository, ClassRepository>();
14            builder.Services.AddScoped<IStudentRepository, StudentRepository>();
15
16            builder.AddGraphQLFunction()
17                .AddQueryType<Query>()
18                .AddTypeExtension<SchoolExtensions>()
19                .AddTypeExtension<ClassExtensions>()
20                .AddTypeExtension<StudentExtensions>();
21        }
22    }
23}
24

Run the Application and navigate in a browser to it's one route and you should get see the Banana Cake Pop UI to be able to view your schema.

Banana Cake Pop UI showing Scheme Reference

You can also test out queries selecting just the data you want even on related items.

We could even start with selecting a specific student and pull in their related class and school info.

The Bad News

All of this is great and in fact, even more, functionality is available to be added, but there is some bad news. Not all of Hot Chocolates functionality actually works in an Azure Function, specifically authentication.

You can read about Hot Chocolates implementation of Authentication and Authorization here however it uses ASP.NET Core authentication middleware and Authorize attributes which do not work in Azure Functions. So unless you want your Graph QL API to be fully public you may be out of luck with this being a solution.

Code for this Demo

Now for the good news, if you want to try this without typing all the code, you can get a copy of it from my GitHub here.

https://github.com/timgriff84/AzureFunctionWithGraphApi

JavaScript frameworks explained to an ASP.NET dev

8 February 2021

For most of my career I've been an ASP.NET dev and a JavaScript dev. If I was going to say I was more of an expert in one of them it would be the .NET side of things but I've never really lost touch with JavaScript.

Right now I think it's fair to say technologies in the world are starting to shift how we build websites, with JavaScript frameworks reaching a point with features like static site generation where they actually now offer a decent performance incentive to use them. At some point Blazor may get to a point where it reverses this, but right now there's a compelling argument to move.

For a ASP.NET dev this can be a daunting task. You might be thinking of trying out a headless CMS with a JavaScript front end, but just take a look at this screen grab from Prismic's sdk list.

There's 7 different JavaScript based SDK's listed there! Over half of the total and none of them are that Angular thing you had heard about. Where do you start?

Lets compare to .NET

Well recently I've been updating my JS skills again trying out some of the frameworks I hadn't used before, so I thought I'd share some learnings. The good news is as always it's not really as different as it first seems. To take some of the pain out of understanding what all these frameworks are I thought it would be good to try and relate them back to .NET and what the almost equivalent is.

Assembly Code

No not actual assembler but what does our code actually compile to. In the .NET world we have CIL (Common Intermediate Language), previously known as MSIL (Microsoft Intermediate Language) that our C#, F#, VB etc all compile down to before then being converted to the correct machine code for where they run.

In the front end world think of JavaScript being a bit like this (apart from the fact you actually write JavaScript and we don't write CIL).

View Engine

To render views into HTML, in the ASP.NET world we have Razor, but not just Razor. We also have WebForm, Brail, Bellevue, NDjango (see more here), it just happens that we mostly just use Razor.

I see the equivalents of these being ReactJS, VueJS and Angular. Its not an exact match as they also aren't exact equivalents or each other, but they're largely your functionality that will take a model and turn it into HTML.

Web Application Framework

The problem with the name framework is it applies to basically anything, but this is what I'm going with for describing ASP.NET MVC/ASP.NET Razor Pages/Web Forms, you know all those things built on-top of .NET that make it a website rather than a desktop app. They do things like routing, organising our files into controller and view folders, know how to respond to http requests etc.

Here we have Next.js, Nuxt.js and maybe Gatsby. The link between these and View Engine is a bit stronger than the ASP.NET MVC world as you essentially have a one to one mapping Next.js -> React, Nuxt.js -> Vue but they are what adds routing, static site generation and organization to your code.

Lower Level Framework

Now this one could be wrong :)

In .NET we have different version of the framework. e.g. .NET Framework /3.5/4, .NET Core, .NET 5, Mono. On the front end side they have Node.

Languages

In .NET we have choices including C#, F#, VB among other.

JavaScript has JavaScript (which I know I said was assembly), TypeScript, Coffee Script maybe more.

Not so daunting

There's probably a bunch of flaws with my comparison list and reasons people can point out why things I've said are the same are in fact different, but my point was really to show that while .NET may appear as one button on a SDK list alongside 7 JavaScript based SDK's its not that different. Out of the 7 Node is based on JavaScript. Vue and React are based on Node, and Next/Gatsby/Nuxt are based on Vue/React. There just isn't the same concept of all of it being built by one company or one particular combination being dominant in the same way that ASP.NET MVC + C# + Razor has been for the last generation of .NET websites.