Mely Castañeda Mely Castañeda - 4 months ago 59
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.


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