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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 }) | |
| .ToListAsync(); |
We can verify that the query generated by the previous statement is correct:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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] |
Regards!
Felipe Gavilán 