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

Question

I am having a table like this :

EmployeeId  EmployeeName ItemName
4           Ganesh       Key Board
4           Ganesh       Processor
1           Jignesh      Key Board
1           Jignesh      Mouse
1           Jignesh      Processor
3           Rakesh       Key Board
2           Tejas        Key Board
2           Tejas        Mouse
2           Tejas        Processor

I need to query this as if the itemname is different for the same employeeid and employeename we should have the items as ',' separated.

Like the one which is given below :

EmployeeId  EmployeeName ItemName
1           Jignesh      Key Board, Mouse, Processor
2           Tejas        Key Board, Mouse, Processor
3           Rakesh       Key Board
4           Ganesh       Key Board, Processor

Here is the SQL Query for this:

Could anyone help me to convert the above SQL Query into Lambda Expression?

Answer 1

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