How to retrieve multiple column values with same foreign key id using Linq method Syntax in C#?

Question

This works:

var query = (from user in _context.Users
             join role in _context.UserRoles on user.UserID equals role.UserId
             where user.Username == username
             select role.Role).ToArray();

How do I do the same in the method syntax?

//here role.Role has multiple values

var query2 = _context.Users.Join(_context.UserRoles, u=>u.UserID,ur=>ur.UserId,
                 (u,ur)=> new { ur.Role }).ToArray();

The above code throws an error:

Cannot implicitly convert type<anonymous // type string Role>[] to 'string[]'

Answer 1

Better to stay with LINQ query syntax which is closer to the SQL and you can easily modify your query. Anyway here is your translation:

 var query2 = _context.Users
    .Where(u => u.Username == username)
    .Join(_context.UserRoles, u => u.UserID, ur => ur.UserId, (u,ur) => ur.Role)
    .ToArray();

Answer 2

So you have Users and UserRoles. There is a one-to-many relation between Users and UserRoles: every User has zero or more UserRoles; every UseRole belongs to exactly one User, namely the User that the foreign key UserId refers to.

You also have a UserName, and you want all Users that have this name, each User with its UserRoles.

Note: you didn't say that UserName is unique, so It can be, that after the Where you still have several Users with userName "Will Smith".

Short answer

string userName = "Will Smith";
var result = dbContext.Users
    .Where(user => user.UserName == userName)
    .Join(dbContext.UserRoles,

        user => user.Id,               // from each User take the Id
        userRole => userRole.UserId,   // from each UserRole take the foreign key

        (user, userRole) => userRole);

Or the other way round: start at UserRoles and keep only those UserRoles that have aUser with userName:

dbContext.UserRoles.Where (userRole => 
    dbContext.Users
        .Where(user => user.Id == userRole.UserId  // get the User of this Role
                    && user.UserName == userName)  // check the name of this User
        .Any() );

There's room for improvement

If user [10] has 20 UserRoles, then every UserRole of User [10] will have a foreign key with a value 10. You will be transferring this value 20 times.

If there are several "Will Smith", you will have one big sequence with all the UserRoles of all "Will Smiths" randomly mixed.

Your solution will result in:

UserId UserRole
10     Administator,
10     User
25     Backup
10     Backup
18     User
25     User

Wouldn't it be more efficient to group the User, so you have something like:

UserId    UserRoles
10        { Administator, User, Backup }
18        { User }
25        { User, Backup }
22        <no UserRoles yet>

Note: the result is slightly different: you also get the Users that have no role yet.

Whenever you have items with their zero or more subitems, like Schools with their Students, Customers with their Orders, or Users with their UserRoles, consider to use one of the overloads of Queryable.GroupJoin.

Most of the times I use the overload with a parameter resultSelector. This way you can specify exactly which properties you want, and in what format:

var usersWithTheirUserRoles = dbContext.Users

    // keep only the users with a specific UserName
    .Where(user => user.UserName == userName)

    // fetch some properties of the remaining users and their UserRoles
    .GroupJoin(dbContext.UserRoles,

        user => user.Id,               // from each User take the Id
        userRole => userRole.UserId,   // from each UserRole take the foreign key

        // parameter resultSelector: take each user, with its zero or more userRoles
        // to make one new:
        (user, userRolesOfThisUser) => new
        {
            // Select only the user parameters that you plan to use:
            Id = user.Id,
            Address = user.Address,
            ...

            // select the zero or more user roles of this user
            UserRoles = userRolesOfThisUser.Select(userRole => new
            {
                // Select only the properties that you plan to use
                Id = userRole.Id,
                Description = userRole.Description,
                ...

                // not needed, you've already got the value:
                // UserId = userRole.UserId,
            })
            .ToList(),
        });

Advantages:

• You also get the Users that have no UserRoles yet (in your original requirement not a problem)
• Efficiency: Every property of a User is sent only once. An inner join, or left outer join would send the same properties of the User over and over again
• Efficiency: You transfer only the properties that you really need
• You can deviate from your original tables. If you want to leave out UserRoles, or calculate some properties, like UserRoleCount you can just do it. This makes it easier to change your database tables in the future, without changing this query