Linq lambda表达式(GROUP_CONCAT/STRING_AGG)中的XML PATH和Stuff等价于什么? [英] What is the equivalent of XML PATH and Stuff in Linq lambda expression (GROUP_CONCAT/STRING_AGG)?

问题描述

我有一张这样的桌子:

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

我需要查询一下，就好像相同的employeeid和employeename的项目名称不同，我们应该将项目，"分开.

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

就像下面给出的那样:

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

这是对此的SQL查询:

Here is the SQL Query for this:

有人可以帮助我将上述SQL查询转换为Lambda表达式吗?

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

推荐答案

我假设Lambda expression是指Linq语句(例如EF或Linq2Sql).

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

所示的FOR XML PATH和STUFF示例是一种可解决Sql中缺少 GROUP_CONCAT或LISTAGG的黑客服务器.最后在Sql 2017中有 STRING_AGG

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

您根本不需要在LINQ中重现hack-相反，只需将感兴趣的雇员集的所有行加载到内存中，GroupBy所需的密钥，然后在选择的投影中使用String.Join :

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))
       });

其中employeeItems是Employee和Items之间的联接的投影:

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"}
};

结果:

1 Ganesh Keyboard,Mouse
2 John   Keyboard

这篇关于Linq lambda表达式(GROUP_CONCAT/STRING_AGG)中的XML PATH和Stuff等价于什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！