0

I need to list all users, along with all the roles each user has.

I know I could query the users table, and then use UserManager<ApplicationUser> to query the roles for each user. But this would be incredibly inefficient. This should be possible with a single query?

I start with something like this:

Users = await (from u in DbContext.Users
               orderby u.Name
               select new UserSummaryViewModel
               {
                   Id = u.Id,
                   Name = u.Name,
                   Roles = u.UserRoles  // ERROR HERE!
                       .Select(ur => ur.Role.Name)
               })
               .ToListAsync();

This won't work because ApplicationUser has no UserRoles navigation property. So I changed it to this:

Users = await (from u in DbContext.Users
               orderby u.Name
               select new UserSummaryViewModel
               {
                   Id = u.Id,
                   Name = u.Name,
                   Roles = DbContext.UserRoles
                       .Where(ur => ur.UserId == u.Id)
                       .Select(ur => ur.Role.Name)  // ERROR HERE!
               })
               .ToListAsync();

But this won't work because UserRole has no Role navgiation property.

I'm not sure why Microsoft didn't add those navigation properties. But it seems like this shouldn't be so difficult.

Can anyone see a way to do this?

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466

1 Answers1

2

There is post here for .Net core 2.1. I think it is still valid: You should change the ApplicatinUser And ApplicationRole classes to include the relationship then you can easily query like this:

var usersWithRoles = userManager.Users.Include(u => u.UserRoles).ThenInclude(ur => ur.Role).ToList();
MD Zand
  • 2,366
  • 3
  • 14
  • 25