Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Aggregate or join strings in linq to sql query (SQL Server)

Given a table like

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

I want to produce a result like

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

I tried something like

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

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

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

So, how can i do this?

like image 909
ariel Avatar asked Aug 06 '10 19:08

ariel


People also ask

How does a LINQ query transform to a SQL query?

LINQ to SQL translates the queries you write into equivalent SQL queries and sends them to the server for processing. More specifically, your application uses the LINQ to SQL API to request query execution. The LINQ to SQL provider then transforms the query into SQL text and delegates execution to the ADO provider.

Is LINQ faster than SQL?

More importantly: when it comes to querying databases, LINQ is in most cases a significantly more productive querying language than SQL. Compared to SQL, LINQ is simpler, tidier, and higher-level. It's rather like comparing C# to C++.

What is aggregate function LINQ?

An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of greater significance or measurement such as a set. Aggregate functions return a single value. Common Aggregate Functions are: SUM() : Returns the sum of column values.


1 Answers

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));
}
like image 52
Sisiutl Avatar answered Oct 12 '22 09:10

Sisiutl