title: Aggregate or join strings in linq to sql query (SQL Server)
body: <p>Given a table like</p>
  
  <pre><code>ID | Name | City
  1  | X    | Y
  2  | Z    | Y
  3  | W    | K
  </code></pre>
  
  <p>I want to produce a result like</p>
  
  <pre><code>ID | Description
  1  | Y (X, Z)
  3  | K (W)
  </code></pre>
  
  <p>I tried something like</p>
  
  <pre><code>From C In Clients Group C By C.ID, C.City _
  Into G = Group Select New With {.ID = ID, .Description = City &amp; _
  " (" &amp; (From C In Clients Select C.Name).Aggregate(Function(X, Y) X &amp; ", " &amp; Y) &amp; ")"}
  </code></pre>
  
  <p>Which gave me an error "The query operator 'Aggregate' is not supported."
  Also tried</p>
  
  <pre><code>From C In Clients Group C By C.ID, C.City _
  Into G = Group Select New With {.ID = ID, .Description = City &amp; _
  " (" &amp; String.Join((From C In Clients Select C.Name).ToArray, ", ") &amp; ")"}
  </code></pre>
  
  <p>Which gave me the error "no supported translation to SQL"</p>
  
  <p>So, how can i do this?</p>