CODEWITHSUNDEEP
c#asp.netsql-serverentity-frameworklinq
Mely Castañeda
Mely Castañeda

Reputation: 23

How to convert this query from SQL to LINQ

I'm working on a query to get the role assigned to each user and display it on a table. The entities are (sorry for the picture):

enter image description here

I am dealing with the structure of the database which provides Identity .net. The SQL query came out as follows:

SELECT AspNetUsers.Id, AspNetUsers.UserName, AspNetRoles.Name
FROM AspNetUsers
INNER JOIN 
(AspNetRoles INNER JOIN AspNetUserRoles 
ON AspNetRoles.Id LIKE AspNetUserRoles.RoleId)
ON AspNetUsers.Id LIKE AspNetUserRoles.UserId

I'd like to convert that query in LINQ or someone knows any other way to obtain the user's role for display in a table? I'm working in Asp.NET Web Forms. Thanks

Upvotes: 0

Views: 337

Answers (4)

Mely Castañeda
Mely Castañeda

Reputation: 23

Thank you @ad-net, your answer helped me to check my foreign keys. The correct query is:

var result = from u in le.AspNetUsers
             select new
             {
                 Id = u.Id,
                 UserName = u.UserName,
                 Correo = u.Email,
                 Roles = u.AspNetRoles.Select(r => r.Name)
             };

Thanks to all for your answers. :)

Upvotes: 0

AD.Net
AD.Net

Reputation: 13409

If foreign keys are set up properly, you can do this:

var result = from u in AspNetUsers select new 
                {
                   Id = u.Id, UserName = u. UserName,
                   Roles = u.AspNetUserRoles.Select(r=>r.Name)
                }

You'll have a cleaner structure like 

          {Id, UserName, IEnumerable<string>Roles}

Upvotes: 1

M B
M B

Reputation: 2326

var Users = le.AspNetUsers.Include("AspNetUserRoles").ThenInclude("AspNetRoles").ToList();

aspnet users can have more than one role assigned. You can use linq Select method to get specific properties and map it to a new object.
http://www.dotnetlearners.com/linq/linq-to-sql-writing-select-query.aspx

Upvotes: 0

Alex Kozlowski
Alex Kozlowski

Reputation: 226

You can try and use this tool: http://www.sqltolinq.com/

Upvotes: 2

Related Questions