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[]'

score 1 · Accepted Answer · answered Nov 01 '20 at 07:20

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();

answered Nov 01 '20 at 07:20

Svyatoslav Danyliv

21,911
3
16
32

Thanks for the answer. I am a fresher and am trying to learn different ways to do things. So I tried to do it in Method syntax as well. – Madhukar Nov 01 '20 at 07:54
Install ReShaper extension. It can convert query syntax to method chain in one click. – Svyatoslav Danyliv Nov 01 '20 at 08:33
Ohh, I dont know that will try for sure thanks again. – Madhukar Nov 01 '20 at 08:44
@Madhukar please take note of what @Svyatoslav said as so many devs get it wrong.... `Better to stay with LINQ query syntax which is closer to the SQL and you can easily modify your query.` <--- this is gold stuff.. and you should take note if you are working with `dbcontext` – Seabizkit Nov 01 '20 at 09:13
@Seabizkit thanks for the advice and I will surely follow that. – Madhukar Nov 01 '20 at 09:38

score 1 · Answer 2 · answered Nov 02 '20 at 09:14

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

Thanks for the reply Sir. This has a lot of points to take note. As you said I have not mentioned that the UserName is unique so there is that possibility. The table User has users with distinct name as I made sure in the code that if tried to insert same username it returns a message stating "Username already exists". The UserRole table consists of one or more Roles for a single username and username may have zero or more role. So I wanted to retrive all the Roles for a single UserName matching 'username' string — Madhukar, Nov 03 '20 at 03:52

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

2 Answers2

Short answer

There's room for improvement