List of Users with list of Roles in Identity 2.2.1

Question

So I've been searching Google and SO. Feels like this question has been asked many times, but no answer has helped me but I feel like I'm getting close. However, I'm new to LINQ and Lambda and don't have the knowledge to do what I want.

Desired Result

User                   Roles
-----------------------------------------
John                   Admin
Jane                   Staff, HR, Payroll
MyCoolUserName         User

I got pretty close from this post and this post. Here's what I got so far.

ViewModel:

public class UsersViewModel {
    [Display(Name = "User")]
    public ApplicationUser User { get; set; }

    [Display(Name = "Roles")]
    public string Roles { get; set; }
}

Controller:

Trial #1

This solution returns blanks for the roles, and I had to add this to my web.config file: <roleManager enabled="true" />

public class UsersController : Controller {
    public async Task Index() {
        var allUsers = await db.Users.ToListAsync();
        var users = new List();
        foreach (var user in allUsers) {
            String[] roles = Roles.GetRolesForUser(user.UserName);
            users.Add(new UsersViewModel {User = u, Roles = String.Join(",", roles.ToArray())});
        }
        return View(users);
    }
}

Trial #2

This solution returns one row per user per role, but only returns the RoleId

public class UsersController : Controller {
    public async Task Index() {
        var allUsers = await db.Users.ToListAsync();
        var users = allUsers.Select(u => new UsersViewModel {User = u, Roles = String.Join(",", u.Roles.Select(r => r.RoleId))}).ToList();
        return View(users);
    }
}

Here's what I get for Trial #2 when I change RoleId to RoleName:

I can tell that in trial #2, u.Roles is linked to the UserRoles table. Logically, I know that what I want is to inner join the Roles table and get the name there.

I hope someone can help me out? Thanks in advance. Sample Project

Answer 1

Thanks to @Kienct89 and a discussion we had, I accidentally stumbled upon the answer myself. Here's what I got, and if anyone can or wants to improve on it, please do so.

e.g.: I don't know if it's better to get all the roles into a variable first and iterate over that, you see this in version 1, or not like in version 2.

Version 1

public class UsersController : Controller {
    public async Task Index() {
        var allUsers = await db.Users.ToListAsync();
        var users = allUsers.Select(u => new UsersViewModel {User = u, Roles = String.Join(",", db.Roles.Where(role => role.Users.Any(user => user.UserId == u.Id)).Select(r => r.Name))}).ToList();
        return View(users);
    }
}

Version 2 (preferred?)

public class UsersController : Controller {
    public async Task Index() {
        var allUsers = await db.Users.ToListAsync();

        // set all roles to a variable, so that we don't hit the database for every user iteration
        // is this true?
        var allRoles = await db.Roles.ToListAsync();

        var users = allUsers.Select(u => new UsersViewModel {User = u, Roles = String.Join(",", allRoles.Where(role => role.Users.Any(user => user.UserId == u.Id)).Select(r => r.Name))}).ToList();
        return View(users);
    }
}

I feel like version 2 is more efficient in that it will not hit the database to get the roles for every user. Instead it has the roles in a variable. I'm not sure if I am right, but I would like to be enlightened and learn from anyone who does know.