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?

Yodle

Please update your question with the text that you have in your images. – Fugere

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

Chapel

But the result I am getting is : – Yodle

1 Ganesh Keyborad ,Mouse 2 John Keyborad Could you please tell me how to truncate the Space between "Keyboard" and "," ? – Yodle

Correct me if I'm wrong, but doesn't this mean that the resulting SQL statement returns more data than it needs to? Since the ToList() is done before the Select statement, it will unnecessarily select all columns from the table. Is there a way to do this at the DB level? – Dipteran

@IanParry technically, yes you are right, if you select the minimal columns in a custom Select into either a new POCO or an anon class, then yes, IO would be slightly reduced, and more importantly, SQL would have a chance to use a narrower nonclustered or covering index. But micro optimization of generated SQL is one of the tenets that ORMs seem to avoid - the 'norm' for ORMs like EF is to simply fetch the whole entity (i.e. full row, off the table cluster), and go from there. But in 2017, we want to be using STRING_AGG directly in the DB, and none of the above nonsense will be necessary. – Chapel