Three-way join in Entity Framework Core to retrieve all roles that a user has

Question

I'm very new to Entity Framework Core, so I'm concerned about whether I've done this query in the most efficient way possible.

The database I'm querying looks like this:

Basically, UserRoles is a a "linking table" between the User and the Roles in order to create a one-to-many relationship. If a user exists at all, they will always have at least one role, but they could have several roles.

I want to get the role name based on the NT ID of the user.

This is something I plan to do fairly often, so I separated it out into a separate utility (rather than having it in the controller).

In SQL, I'd just do two inner joins. However, what I did here is to query UserRoles (rather than using Join).

public class UserUtilities
{
    public static List<Roles> GetRoles(string ntid)
    {
        var ctx = new MyLiveDocsContext();
        List<Roles> roles =
            (from ur in ctx.UserRoles
             where ur.User.Ntid == ntid
             select ur.Role).ToList();

        return roles;
    }
}

Ive tested this and it does what I want, but I'm wondering if this is the best way to do this. For example, would it be better to use an explicit join instead?

Answer 1

For this isolated piece of code, this is the way to go. Generally, in ORMs that support LINQ, navigation properties (like ur.Roles) should be used wherever possible. Usingjoin should be reserved for custom joins where no navigation properties exist.

Reasons:

1. Joins are far more verbose than navigation properties, making code harder to read and causing repetitive code.

2. Joins are error-prone. It's easy to match the wrong fields. A navigation property, once configured correctly, will always generate a correct SQL join.

3. Navigation properties, if well-named, are more expressive. In a join the cardinality of a relationship isn't always obvious. In navigation properties it is. You know what you're looking at when reading Role vs Roles. Unfortunately, sometimes people tend to be sloppy in this area and they just use the generated class and property names as an ORM generated them, which may result in any mixture of plural class names, plural reference property names, and singular collection property names.

One exception to this rule is when you want to overrule the ORM's SQL generation. Entity Framework will generate an OUTER JOIN for non-required navigation properties (i.e. where the "1"-side is optional, 0..1-n, 0..1-1). There may be cases where INNER JOIN performs better and the results with null navigation properties aren't interesting anyway. In those cases, using a manual join, overriding a navigation property, could be considered.

Side note: as said in a comment, it's recommended to use disposable objects, like a DbContext, in a using statement.