SolvedPrivate

How can I do this SQL in LINQ?

Posted on 2014-11-23
10
54 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ado.net AddWithValue when using IN condition 7 45
parse string in c# 5 41
C#: How do I test for null objects and a dub a value? 2 37
Adding Rows to Grid 4 32
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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