Implementing a recursive projection query in C# and Entity Framework Core

Imagine we have a table in the database called Category where each row may have a parent Category such that we end up with a hierarchical tree of categories and subcategories to some unknown depth.

Each Category contains an optional ParentCategoryId forming the basis of the table structure.

In C# you would have the corresponding class:

Using Entity Framework Core this would be configured like this:

So let's say we wish to produce a query to return a list of of the root most categories such that they also contain any child categories and in turn these child categories contain their subcategories and so on. Our first thought might be something like:

Unfortunately this will not work as the first levels subcategories children will not be loaded.

One way to get around this would be to load all categories first by enumerating a general select all query, like the following:

This is ok for a small amount of data, but what if you have thousands or millions of rows and perhaps you are only interested in one root item this is not very efficient.

Furthermore we more often than not don’t want unnecessary data to be queried as typically we might be returning the data via a rest endpoint so would use some kind of DTO representation of our class, for example:

Finally, one way to query the hierarchical would be to write a recursive method which applies a projection of Category to CategoryDTO and queries all subcategories as needed to a maximum specified depth.

Why do we need to specify a maximum depth? Well the recursive function which ultimately generates SQL needs to know when to stop and we want to achieve all this in a single query.

To do this we create a method which contains the required projection, in which makes a recursive call back to itself.

For each layer down we apply the projection recursively to the SubCategories property until we read the maximum depth. Ideally when picking the maximum depth a knowledge of the depth the hierarchy extends to is helpful to ensure nothing is missed. Going a few levels deeper than required is not a big problem as the underlying SQL left joins will just be populated with null data so not too costly.

Finally to use this we simply query the categories in the typical EF way, but noting for the root we only include Categories with a null ParentCategory and in the Select we initiate the recursive call to the projection method passing in a maximum depth and a starting depth of 0.

Thankyou and enjoy!