Mely Castañeda Mely Castañeda - 1 year ago 113
ASP.NET (C#) Question

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
(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.

Answer Source

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. :)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download