Querying EF Core identity roles

Question

I'm using .NET Core 3.1 and I want to query the users in my database.

So I modified my ApplicationUser class as follows:

public class ApplicationUser : IdentityUser
{
    public ICollection<IdentityUserRole<string>> UserRoles { get; set; }
}

And now I'm trying to query the Users.

var users = from u in DbContext.Users
            select new UserViewModel
            {
                Email = u.Email,
                EmailConfirmed = u.EmailConfirmed,
                Phone = u.PhoneNumber,
                Roles = u.UserRoles.Select(r => r.Role.Name) // Whoops! Won't work
            };

The problem is that UserRoles have a RoleId property, but they don't have a Role property.

So how would I get the name of those roles?

Update:

Based on Jawad's comments, I changed my code as follows:

public class ApplicationUser : IdentityUser
{
    public ICollection<IdentityRole> Roles { get; set; }
}

And

Users = (from u in DbContext.Users
         select new UserViewModel
         {
             Email = u.Email,
             EmailConfirmed = u.EmailConfirmed,
             Phone = u.PhoneNumber,
             Roles = u.Roles.Select(r => r.Name)
         })
         .ToList();

I was thinking I couldn't do that because it's a many-to-many relationship. But in fact, it does compile. However, when I run it I get an error.

Invalid column name 'ApplicationUserId'.

I'm not sure where that column name is being referenced. It doesn't appear to be a column name in the database.

Update 2:

Turns out, the error above is because a database migration was pending. However, I didn't want to change the database. As I initially suspected, I can't do ApplicationUser.ICollection<IdentityRole> because there is an intermediate table.

So I got this to run, but it returned no results (because there are no foreign keys from IdentityRole to ApplicationUser.

So, the question stands: how can I query users along with their roles?

Answer 1

You should create a new class called something like ApplicationUserRole that inherits from IdentityUserRole<string> then you just need to define the relationships between ApplicationUser -> ApplicationUserRole and ApplicationRole -> ApplicationUserRole.

modelBuilder.Entity<ApplicationUserRole>(entity =>
{
    entity
      .HasOne(x => x.Role)
      .WithMany(x => x.UserRoles)
      .HasForeignKey(x => x.RoleId);

    entity
      .HasOne(x => x.User)
      .WithMany(x => x.UserRoles)
      .HasForeignKey(x => x.UserId);
 });

You should also replace any references to the Identity classes with your Application classes.

public class ApplicationUserRole : IdentityUserRole<string>
{
    public ApplicationUser User { get; set; }

    public ApplicationRole Role { get; set; }
}

public class ApplicationUser : IdentityUser<string>
{
    public ICollection<ApplicationUserRole> UserRoles { get; set; }
}

public class ApplicationRole : IdentityRole<string>
{
    public ICollection<ApplicationUserRole> UserRoles { get; set; }
}

Answer 2

So, I don't know why IdentityUserRole doesn't include an IdentityRole property. This seems to be an unnecessary limitation.

In the end, I just changed my query.

var users = await (from u in DbContext.Users
                   join ur in DbContext.UserRoles on u.Id equals ur.UserId
                   join r in DbContext.Roles on ur.RoleId equals r.Id
                   select new
                   {
                       u.Email,
                       u.EmailConfirmed,
                       u.PhoneNumber,
                       Role = r.Name
                   })
                   .ToListAsync();

Users = (from u in users
         group u.Role by u into g
         select new UserViewModel
         {
             Email = g.Key.Email,
             EmailConfirmed = g.Key.EmailConfirmed,
             Phone = g.Key.PhoneNumber,
             Roles = string.Join(", ", g)
         })
         .ToList();

I couldn't get the group by to work within the query so I'm group it after the data has been retrieved.

Anyway, it works.