Camillia
asked on
How can I do this SQL in LINQ?
This is MVC Identity. Please see attached screenshot.
If this is too complicated, I'll just do this in a stored proc.
I want to get a list of users and their roles. The table structure has changed with MVC Identity. I know I can get the users and roles like this but I want to join them together
I tried joining them together but looks like I need to drill down to RoleId somehow.
This is what I want
I tried this but this brings back 4 rows. It should be 2. I think the left join is wrong.
If this is too complicated, I'll just do this in a stored proc.
I want to get a list of users and their roles. The table structure has changed with MVC Identity. I know I can get the users and roles like this but I want to join them together
var context = new ApplicationDbContext();
var allUsers = context.Users.ToList();
var allRoles = context.Roles.ToList();
I tried joining them together but looks like I need to drill down to RoleId somehow.
This is what I want
select u.Id,
u.Email,
U.username,
r.Name,
ur.userId,
ur. RoleId
from [dbo].[AspNetUsers] u
left join [dbo].[AspNetUserRoles] ur on ur.userId = u.Id
left join [dbo].[AspNetRoles] r on r.id =ur.RoleId
I tried this but this brings back 4 rows. It should be 2. I think the left join is wrong.
var users = (from r in roles
join u in allusers on r.Users.Select(m => m.UserId).ToString() equals u.Roles.Select(m =>m.UserId).ToString()
select new
{
u.Id,
u.UserName,
u.Email,
r.Name,
RoleId = r.Id
}).ToList();
Identity.png
ASKER
Thanks, Richard! I thought no one would respond. Was about to do it in a stored proc. Let me try it and will post back right away.
ASKER
No, there's no UserRoles table. Even tho you see it in the screenshot (SQL)...in MVC Identity...it seems like I have to drill down in the "Role" to get do a join.
See attached screenshot. My LINQ above works but it's the left join that I'm missing because there might not be roles so I have to do a left join.
roles.png
See attached screenshot. My LINQ above works but it's the left join that I'm missing because there might not be roles so I have to do a left join.
roles.png
ASKER
Tried this as well but still get 4 rows. See attached screeshot. I think this is because I have 2 roles. I must be missing another join.
var test = (from r in roles
join u in allusers on r.Users.Select(m => m.UserId).ToString() equals u.Roles.Select(m => m.UserId).ToString()
into JoinedEmpDept
from u in JoinedEmpDept.DefaultIfEmpty()
select new
{
u.Id,
u.UserName,
u.Email,
r.Name,
RoleId = r.Id
}).ToList();
roles2.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is another solution. It seems like out of the box the AccountController has methods to get the roles for a user.
Hope this helps!
var account = new AccountController();
var userRoles = account.UserManager.GetRoles(user.Id);
Hope this helps!
ASKER
oh, let me take a look
ASKER
Can it be joined to context.Users to get a list of users and their corresponding roles? like that what I was trying to do above? if not, looks like a stored proc is easier
ASKER
The solution in ID: 40462216 gives me the role for one user. That's good.
I still wonder if I can get a list of users with their roles. I Googled a lot last night but not much came up.
I still wonder if I can get a list of users with their roles. I Googled a lot last night but not much came up.
ASKER
I'll open another question. You've answered this one. Thanks
You can try something like below:
Open in new window
Hope this helps!