汇总或联接linq中的字符串以进行SQL查询(SQL Server) [英] Aggregate or join strings in linq to sql query (SQL Server)

问题描述

给出一个像这样的表

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) & ")"}

给我一​​个错误不支持查询运算符'Aggregate'". 也尝试过

Which gave me an error "The query operator 'Aggregate' is not supported." Also tried

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的翻译"

Which gave me the error "no supported translation to SQL"

那么，我该怎么做?

推荐答案

我用C#破解了它，它似乎可以提供您想要的东西.我会将翻译的工作交给VB.

I hacked this in C# and it seems to give what you want. I'll leave the translation to VB up to you.

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

这篇关于汇总或联接linq中的字符串以进行SQL查询(SQL Server)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！