Getting Users and Their Role in ASP.NET Core – AspNetUsers and AspNetUserRoles

We know that we can use IdentityDbContext in order to have a useful table schema for a user system in our application. Then, through the DbContext, we will be able to access the list of users of our application, and also the list of roles of our application. However, what happens when we want to query the list of users, and each user with their respective role?

Well, we can do a join. With a join we can relate the data from different tables, in our case, from the AspNetUsers and AspNetUserRoles tables. Then we make a projection, where we extract the required data from both tables. In our case, we want the user’s Id, username, role id and role name:

var listado = await (from user in context.Users
join userRoles in context.UserRoles on user.Id equals userRoles.UserId
join role in context.Roles on userRoles.RoleId equals role.Id
select new { UserId = user.Id, UserName = user.UserName, RoleId = role.Id, RoleName = role.Name })

view raw


hosted with ❤ by GitHub

We can verify that the query generated by the previous statement is correct:

SELECT [a].[Id] AS [UserId], [a].[UserName], [a1].[Id] AS [RoleId], [a1].[Name] AS [RoleName]
FROM [AspNetUsers] AS [a]
INNER JOIN [AspNetUserRoles] AS [a0] ON [a].[Id] = [a0].[UserId]
INNER JOIN [AspNetRoles] AS [a1] ON [a0].[RoleId] = [a1].[Id]

view raw


hosted with ❤ by GitHub


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s