How to create a Stuff and XML PATH(SQL) in Linq or Lambda

c# entity-framework-core lambda linq sql-server

Question

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
1
1
6/3/2018 7:54:18 AM

Accepted Answer

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

3
6/3/2018 2:40:20 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow