Forem: Konstantin Triger

Hierarchical Queries in SQL with C# and Entity Framework Core

Konstantin Triger — Wed, 29 Apr 2020 16:30:49 +0000

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.

New LINQ for EF Core

Konstantin Triger — Tue, 24 Mar 2020 22:59:02 +0000

There is is an interactive demo site powered by Try .NET.

Fluent MongoDB queries from Java

Konstantin Triger — Sat, 16 Nov 2019 12:33:12 +0000

MongoDB is doing great, firmly holding a prestigious #5 on the db-engines ranking site. It means that we, developers, are using it a lot.

When looking on the official driver API, we can see an effort to make an API as fluent as possible:

collection.find(and(gte("stars", 2), lt("stars", 5), eq("categories", "Bakery")));

Yet, there are many issues:

All fields are Strings without auto completion.
No type safety on operators (Is stars a number?! Is categories a collection (?!) of Strings or …?!).
Do we really like using gte for >= ?! I mean, is it easy to read and understand this expression?
And finally, when the schema changes, we are back to 80' with find-replace…

There is a simple and effective solution to those issues — FluentMongo, which adds the missing ingredient to the API — type safety and Java integration. It lets you use normal Java to write filters, projections, updates, sorts and indexes. For example:

collection.find(builder.filter(r -> r.getStars() >= 2 && r.getStars() < 5 &&
                                    r.getCategories().contains("Bakery")));

You can be both productive with MongoDB and write a maintainable code now!