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

Question

I have 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 request this, as if the element name was different for the same employeeid and employeename , we would have to separate the elements as.,

Like the one 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:

Can someone help me convert the above SQL query into a Lambda expression?

Answer 1

I assume that Lambda expression you mean the Linq operator (for example, EF or Linq2Sql).

The FOR XML PATH and STUFF example shown is a hack to fix the lack of GROUP_CONCAT or LISTAGG on the Sql server .

You do not need to play the hack in LINQ at all - just load all the lines for the set of employees of interest into memory, GroupBy desired key, and then use String.Join in the selected 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 the projection of the connection 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