What is the equivalent of XML PATH and Stuff in Linq lambda expression (GROUP_CONCAT/STRING_AGG)?
Deepa Mani
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?
StuartLC
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: Async thread body loop, It just works, but how?
- Next: yii2 gridview not showing all the left join values using hasMany retionship
Related
Related Related
- 1
Is there a java lambda expression equivalent of the C# linq let?
- 2
Is there a java lambda expression equivalent of the C# linq let?
- 3
& instead of & in STUFF FOR XML PATH
- 4
What really lambda looks like? (not a linq lambda expression)
- 5
What is the equivalent lambda expression for System.out::println
- 6
Linq with Lambda equivalent of SQL
- 7
What is the LINQ/Lambda equivalent to Select Count(*), SUM(someColumn) From SomeTable
- 8
LINQ with Lambda expression - Join, Group By, Sum and Count
- 9
What's the equivalent of GROUP INTO in Linq extension method?
- 10
What's the equivalent of GROUP INTO in Linq extension method?
- 11
How Stuff Works Or Logic of Stuff & XML Path in SQL
- 12
XmlDocumentFragment equivalent in LINQ to XML?
- 13
lambda and linq expression
- 14
Inline lambda in a linq expression?
- 15
Convert this LINQ to Lambda Expression?
- 16
Statement in linq lambda expression
- 17
What is the Java equivalent for LINQ?
- 18
“Stuff and 'For Xml Path'” or UNION to exclude duplicate rows
- 19
Why isn't STUFF and FOR XML PATH not concenating?
- 20
Main Query STUFF FOR XML PATH Not Working
- 21
Invalid object name in a XML Path / Stuff combination
- 22
Lambda expression and equivalent anonymous class
- 23
IndexOf with Linq, accepting lambda expression
- 24
linq lambda expression for sql contains
- 25
How to convert this lambda expression to linq
- 26
Extracting lambda expression from LINQ
- 27
Conditional SELECT in Lambda expression LINQ
- 28
IndexOf with Linq, accepting lambda expression
- 29
outer Join in linq with lambda expression
Comments