I have default project template of ASP.NET MVC 5 web site and I am trying to list all users with role names (not IDs).
The query is:
db.Users.Include(u => u.Roles).ToList()
Then I want to print the role names with something like:
@string.Join(", ", user.Roles.Select(r => r.RoleId))
The problem is that I can reach only RoleId
, not Role class where Name
and other properties are stored.
I could run another select to get all roles and then use that as a lookup. Or write a join in the query directly? I am not sure how because I do not have access to the table with IdentityUserRole
entities that are binding users and roles together.
The root of the problem seems to be the fact that is Roles collection of IdentityUserRole
(not Role
) which contains only RoleId
and UserId
.
public class IdentityUserRole<TKey> {
public virtual TKey RoleId { get; set; }
public virtual TKey UserId { get; set; }
}
I thought that if one want to do N-to-N relationship in EF they should put directly collection of Roles
and then override OnModelCreating
and specify the relationships. This approach seems to complicate browsing the objects from one to another.
Why they decided to include IdentityUserRole
as extra entity? To be able to add extra data to the relationships? At cost of not being able to navigate from users to roles?
The way I do it is:
using (var userManager = new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(new ApplicationContext()))
{
var rolesForUser = await userManager.GetRolesAsync(userId);
// rolesForUser now has a list role classes.
}
The identity team made two managers: RoleManager
for sorting out roles (not user roles though) and UserManager
basically for everything authentication wise. There is also a SignInManager
as well but not needed.
So UserManager
finds users, creates users, deletes users, sends emails .... the list goes on.
So my Action
could look like this:
public async Task<ActionResult> GetRolesForUser(string userId)
{
using (
var userManager =
new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(new ApplicationDbContext())))
{
var rolesForUser = await userManager.GetRolesAsync(userId);
return this.View(rolesForUser);
}
}
To execute raw SQL then you can do something like this:
Create the class that Entity Framework can map to, based on the output of your query:
public class UserWithRole
{
public string UserName {get;set;} // You can alias the SQL output to give these better names
public string Name {get;set;}
}
using (var context = new DbContext())
{
var sql = @"
SELECT AspNetUsers.UserName, AspNetRoles.Name
FROM AspNetUsers
LEFT JOIN AspNetUserRoles ON AspNetUserRoles.UserId = AspNetUsers.Id
LEFT JOIN AspNetRoles ON AspNetRoles.Id = AspNetUserRoles.RoleId
WHERE AspNetUsers.Id = @Id";
var idParam = new SqlParameter("Id", theUserId);
var result = context.Database.ExecuteQuery<UserWithRole>(sql, idParam);
}
Pretty simple!
If you alias your SQL return columns:
SELECT AspNetUSers.UserName, AspNetRoles.Name As RoleName
Then your DTO class can look like this:
public class UserWithRole
{
public string UserName {get;set;}
public string RoleName {get;set;}
}
Which is obviously a lot cleaner.