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

Question

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

asp.net-core asp.net-identity c# entity-framework-core

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

Detilium

View more on Stack Overflow

David Lee · Accepted Answer · 3/23/2018 9:49:50 PM

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

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

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related