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

Question

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

Leonardo Pavanello

View more on Stack Overflow

Maxime Gauthier · Accepted Answer · 6/3/2018 2:40:20 AM

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

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

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related