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