What Is the Equivalent of Xml Path and Stuff in Linq Lambda Expression (Group_Concat/String_Agg)

What is the equivalent of XML PATH and Stuff in Linq lambda expression (GROUP_CONCAT/STRING_AGG)?

I'm assuming by Lambda expression you mean a Linq statement (e.g. to EF or Linq2Sql).

The FOR XML PATH and STUFF example shown was a hack to workaround the lack of GROUP_CONCAT or LISTAGG in Sql Server. Finally in Sql 2017 there is STRING_AGG

You don't need to reproduce the hack at all in LINQ - instead, simply load all rows for the set of employees of interest into memory, GroupBy the required key, and then use String.Join in a select projection:

var result = db.EmployeeItems
      // If you have a filter add the .Where() here ...
      .GroupBy(e => e.EmployeeId)
      .ToList()
      // Because the ToList(), this select projection is not done in the DB
      .Select(eg => new 
       {
          EmployeeId = eg.Key,
          EmployeeName = eg.First().EmployeeName,
          Items = string.Join(",", eg.Select(i => i.ItemName))
       });

Where employeeItems is a projection of the join between Employee and Items:

var employeeItems = new []
{
   new EmployeeItem{EmployeeId = 1, EmployeeName = "Ganesh", ItemName = "Keyboard"},
   new EmployeeItem{EmployeeId = 1, EmployeeName = "Ganesh", ItemName = "Mouse"},
   new EmployeeItem{EmployeeId = 2, EmployeeName = "John", ItemName = "Keyboard"}
};

Result:

1 Ganesh Keyboard,Mouse
2 John   Keyboard

Is there a for xml path equivalent in LINQ to SQL?

This is one of those situations where LINQ to SQL does not shine.

This code should be left in the database - it will be easier maintained and most likely will perform better there. Is there a particular reason you want to move this working code out of the database and into your application code? If it were up to me I would leave this code where it is.

Related Topics