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

var context = new ApplicationDbContext();
var allUsers = context.Users.ToList();
var allRoles = context.Roles.ToList();

Open in new window


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

Open in new window


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();

Open in new window

Identity.png
LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard LeeSoftware EnthusiastCommented:
Hey Camilla,

You can try something like below:

var context = new ApplicationDbContext();
                                
var query = 
    from u in context.Users
        join ur in context.UserRoles on u equals ur.UserId = u.Id
        join r in context.Roles on r equals r.Id = ur.RoleId
    select new {
        Users = u, Roles = r
    }

Open in new window


Hope this helps!
0
CamilliaAuthor Commented:
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.
0
CamilliaAuthor Commented:
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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

CamilliaAuthor Commented:
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();

Open in new window

roles2.png
0
Richard LeeSoftware EnthusiastCommented:
Have you tried this approach.

var userManager = HttpContext.Current.GetOwinContext().GetUserManager<ApplicationUserManager>();
var rolesForUser = userManager.GetRoles(user.Id);

Open in new window


http://typecastexception.com/post/2014/06/22/ASPNET-Identity-20-Customizing-Users-and-Roles.aspx#A-Note-About-IdentityRole-and-IdentityUserRole
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard LeeSoftware EnthusiastCommented:
Here is another solution. It seems like out of the box the AccountController has methods to get the roles for a user.

var account = new AccountController();
var userRoles = account.UserManager.GetRoles(user.Id);

Open in new window


Hope this helps!
0
CamilliaAuthor Commented:
oh, let me take a look
0
CamilliaAuthor Commented:
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
0
CamilliaAuthor Commented:
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.
0
CamilliaAuthor Commented:
I'll open another question. You've answered this one. Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.