1
\$\begingroup\$

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:

enter image description here

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?

\$\endgroup\$
2
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.