SolvedPrivate

How can I do this SQL in LINQ?

Posted on 2014-11-23
10
53 Views
Last Modified: 2016-02-16
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
0
Comment
Question by:Camillia
  • 7
  • 3
10 Comments
 
LVL 18

Expert Comment

by:Richard Lee
ID: 40461986
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
 
LVL 7

Author Comment

by:Camillia
ID: 40462021
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
 
LVL 7

Author Comment

by:Camillia
ID: 40462063
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:Camillia
ID: 40462102
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
 
LVL 18

Accepted Solution

by:
Richard Lee earned 500 total points
ID: 40462216
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
 
LVL 18

Expert Comment

by:Richard Lee
ID: 40462321
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
 
LVL 7

Author Comment

by:Camillia
ID: 40462505
oh, let me take a look
0
 
LVL 7

Author Comment

by:Camillia
ID: 40462529
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
 
LVL 7

Author Comment

by:Camillia
ID: 40462607
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
 
LVL 7

Author Closing Comment

by:Camillia
ID: 40462647
I'll open another question. You've answered this one. Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question