How to create the equivalent code in Linq or lambda?
I'm using Entity Framework Core on Asp.net Core 2.
SELECT
(SELECT
STUFF((SELECT DISTINCT ', ' + Roles.Name
FROM AspNetUsers Users
INNER JOIN AspNetUserRoles UserRoles ON UserRoles.UserId = Users.Id
INNER JOIN AspNetRoles Roles ON Roles.Id = UserRoles.RoleId
WHERE AspNetUsers.Id = UserRoles.UserId
FOR XML PATH('')), 1, 2, '')) AS 'Roles',
AspNetUsers.*
FROM
AspNetUsers AspNetUsers
I would have a query looking like this one. If you profile the query, you'll notice that the STUFF ... FOR XML PATH part is computed in memory by .NET instead of SQL.
var test = (
from u in ctx.Users
join ur in ctx.UserRoles on u.Id equals ur.UserId
join r in ctx.Roles on ur.RoleId equals r.Id
group r by u into g
select new { User = g.Key, Roles = string.Join(",", g.Select(y => y.Name)) }
);
Side note It is much easier to work with the query keeping the user as an object, instead of expanding all of its properties