Retrieving multiple users including their roles with EF Core using ASP.NET Identity

Question

I trying to retrieve multiple users, including their role(s). This is used in a forum, where I want to show the role(s) of users who commented on the thread. I've seen many examples of retrieving the roles for a single IdentityUser using the UserManager and RoleManager, but as mentioned, I have multiple users I wish to retrieve.

I've tried including the roles as a property on the user, like so:

public virtual ICollection<IdentityUserRole<string>> Roles { get; set; }

But this doesn't seem to work. I tried skipping the many-to-many table, and simply include the role entity directly on my user, like this:

public virtual ICollection<IdentityRole> Roles { get; set; }

No difference either, still doesn't work. Basically I just want to join the roles to the user, but I'm not sure how.

Including the IdentityUserRole<string> property, and retrieving my users, gives me the following error:

Unknown column 'x.UserProfile.Roles.UserId1' in 'field list'

Here's my select:

comments = _context.Comments
    .Include(x => x.UserProfile)
    .Include(x => x.UserProfile.Roles)
    .OrderBy(x => x.CreateDate)
    .Where(x => x.ThreadId == id && !user.IgnoredUsers.Contains(x.UserProfile.UserName))
    .ToPagedList(Constants.PAGER_DEFAULT_SMALL, page);

David Lee · Answer 1 · 2018-03-23T21:49:50.930

I just ran into this issue the other day. I found a few resources that said I should establish relationships and set up props in the ApplicationUser object, but I did not want to go down that route. I ended up just using a LINQ query expression to build the data I needed.

var usersWithRoles = (
  from u in _db.Users
  select new
  {
    Id = u.Id,
    Email = u.Email,
    Roles = (
      from ur in _db.UserRoles
      join r in _db.Roles on ur.RoleId equals r.Id
      where ur.UserId == u.Id
      select r.Name).ToArray()
  });

EDIT: This assumes you want the role names in an array of strings. If you are having trouble expanding this query to fit your needs just leave a comment and I will try to help you out.

EDIT: Query below should be closer to your use case. I am unable to test the query so let me know if you run into any errors

public class CommentViewModel
{
    public virtual Comment Comment { get; set; }
    public virtual UserProfile User { get; set; }
    public virtual ICollection<Role> Roles { get; set; }
}

var comments = (
  from c in _context.Comments
  join u in _context.UserProfiles on c.UserId equals u.Id
  select new CommentViewModel
  {
    Comment = c,
    User = u,
    Roles = (
      from ur in _context.UserRoles
      join r in _context.Roles on ur.RoleId equals r.Id
      where ur.UserId == u.Id
      select r)
  });

Does this force me to map every property on my `Comment` entity manually? This is just getting the users, but I'm not sure how to map my `Comment` entity — Detilium, Mar 23 '18 at 21:40
You could setup a viewmodel that has the comment as a property and that way you only need to map the object and not all its underlying props. — David Lee, Mar 23 '18 at 21:42
I find it strange that I can't find any solution using simple lambda expressions and EF fluent API, instead of retrieving all data using LINQ queries. — Detilium, Mar 24 '18 at 11:51
Here is the one I found for using include lambdas and EF fluent API, https://stackoverflow.com/questions/47767267/ef-core-2-how-to-include-roles-navigation-property-on-identityuser. — David Lee, Mar 24 '18 at 19:50

score 0 · Answer 2 · answered Mar 26 '18 at 15:53

You don't need to add a navigation property for roles; that already exists on ApplicationUser via inheritance from IdentityUser. The property is UserRoles, which is a collection of IdentityUserRoles, which is itself just an entity representing the M2M between IdentityUser and IdentityRole. Long and short:

var users = db.Users.Include(x => x.UserRoles).Where(...);

Unfortunately, there's no direct way to actually get the roles themselves here, because IdentityUserRole does not have navigation properties. As a result, you'll need a separate query to get the roles:

var usersRoleIds = users.SelectMany(x => x.UserRoles).Select(x => x.RoleId);
var usersRoles = db.Roles.Where(x => userRoleIds.Contains(x));

Finally, you get the roles for any one particular user via:

var specificUserRoles = usersRoles.Where(x => specificUser.UserRoles.Select(r => r.RoleId).Contains(x.Id));

It's not exactly easy, but you can factor out all this code into some sort of mapping utility class and just return a list of "user" view models with roles already attached.

Retrieving multiple users including their roles with EF Core using ASP.NET Identity

2 Answers2