Hierarchical Queries in SQL with C# and Entity Framework Core

Hierarchies are very common in our lives and therefore in the problems we need to solve. The most familiar cases are organizations, products built from parts and other products, file systems etc.

When an hierarchy is stored in a database table, it's usually represented by rows referencing parent rows, recursively.

Suppose we want to query all the employees (directly or indirectly) reporting to a mid-level manager. Can we do that in SQL?

Of course there is a naive solution to select employees directly reporting to that manager, take their ids, perform the query again, etc. But clearly it's not efficient. Fortunately SQL has a standard support for hierarchical queries in a single and efficient query.

The following example demonstrates an hierarchical query using C# and EF Core using ELINQ:

int? managerId = null; //external parameter - "highest" manager id

DbContext.Staffs.Query(() => {
    var org = SubQuery((Staffs managers, Staffs employees) => {
        var r = SELECT(managers);
        FROM(managers);
        WHERE(managers.ManagerId == managerId);

        UNION_ALL();

        var manager = r.Current();

        SELECT(employees);
        FROM(employees).JOIN(manager).ON(employees.Manager == manager);

        return r;
    });

    WITH(org);

    // At this point org "table" contains the employees we need.
    // We can SELECT, JOIN, or filter it as any other table.

    var result = SELECT(org);
    FROM(org);

    return result;
});

You can run this example "live" here or learn more about EF Core and SQL integration.