Mely Castañeda Mely Castañeda - 1 month ago 23
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
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

Answer

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

Comments