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?
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.
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++.
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.
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));
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With