聚合或连接 linq to sql 查询中的字符串 (SQL Server)

Question

给定一个像

ID | Name | City
1  | X    | Y
2  | Z    | Y
3  | W    | K

我想生成这样的结果，就像

ID | Description
1  | Y (X, Z)
3  | K (W)

我尝试过类似的结果，

From C In Clients Group C By C.ID, C.City _
Into G = Group Select New With {.ID = ID, .Description = City & _
" (" & (From C In Clients Select C.Name).Aggregate(Function(X, Y) X & ", " & Y) & ")"}

这给了我一个错误“不支持查询运算符‘聚合’。” 也尝试过

From C In Clients Group C By C.ID, C.City _
Into G = Group Select New With {.ID = ID, .Description = City & _
" (" & String.Join((From C In Clients Select C.Name).ToArray, ", ") & ")"}

，这给了我错误“不支持 SQL 翻译”

那么，我该怎么做呢？

Answer 1

我用 C# 破解了这个，它似乎给出了你想要的。我将把 VB 的翻译留给你。

var clients = from c in context.Clients 
              group c by c.City into cities 
              select new {
                  ID = cities.First().ID,
                  City = cities.Key, 
                  Names = string.Join(",", (from n in cities select n.Name).ToArray()) 
              };

foreach (var c in clients) {
    Console.WriteLine(string.Format("{0}| {1} ({2})", c.ID, c.City, c.Names));
}

Answer 2

该错误意味着您的 LINQ 操作无法在 TSQL 中的 SQL Server 上执行，正如您所编写的那样。

为了实现您想要的目标，您必须选择/评估尽可能多的基础数据，然后在第二步中执行聚合。两步或更多步骤的过程并不理想，但可以完成。