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

Question

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?

Sisiutl · Accepted Answer · 2010-08-06 22:12:21Z

26

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

answered Aug 6, 2010 at 22:12

Sisiutl

4,9958 gold badges45 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ariel Over a year ago

awesome! i wonder why concatenation + join don't work, and join alone works.. go figure

Merritt Over a year ago

Thanks man. This works like a charm... the trouble for me was the embedded query.

Alex Zhukovskiy Over a year ago

However you do it on client which is unwanted is most cases.

p.campbell · Accepted Answer · 2010-08-06 22:09:44Z

1

The error means that your LINQ operation cannot be performed on SQL Server in TSQL, as you've written it.

To achieve what you want, you'll have to select/evaluate as much of your base data as you can, and then perform the aggregation in a second step. A two-or-more step process isn't ideal, but it can be done.

edited Aug 6, 2010 at 22:09

answered Aug 6, 2010 at 21:50

p.campbell

101k70 gold badges264 silver badges326 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related