String Aggregate in LINQ

In the past I described how to perform string aggregates in T-SQL. In this post I will show how strings can be concatenated in LINQ.

I am using ADO.NET data table as a source for the query, but LINQ being LINQ can pull data pretty much from anything so this example can easily be adjusted.

First things first, let’s create the source. As in T-SQL post I am using ol’ faithful Northwind database and getting data from the Employees table:

Dim oConn As New SqlConnection(sMyConnStr) : oConn.Open()
Dim oComm As New SqlCommand("SELECT Country, FirstName FROM Employees ORDER BY Country, FirstName", oConn)
Dim oAd As New SqlDataAdapter(oComm) : Dim oTable As New DataTable : oAd.Fill(oTable)

This will fill the datatable with employees’ first names and countries

Country FirstName
UK      Anne
UK      Michael
UK      Robert
UK      Steven
USA     Andrew
USA     Janet
USA     Laura
USA     Margaret
USA     Nancy

And now we want to group this by the country, combining first names into comma separated string. Consider following LINQ statement:

From oRow In oTable Group By Country = oRow.Field(Of String)("Country") Into Group _
Select Country, Employees = Group.Aggregate(Of StringBuilder)(New StringBuilder, _
Function(Current As StringBuilder, row As DataRow) _
Current.AppendFormat(",{0}", row.Field(Of String)("FirstName"))).ToString.Substring(1)

The statement performs grouping by Country field (Group By … Into) and uses custom aggregate (Group.Aggregate). The custom aggregate uses empty StringBuilder as a seed and StringBuilder’s AppendFormat function to collect first names prepended by commas. At the end stringbuilder is converted to string and leading extra comma is removed ( ToString.Substring(1) )

And here’s the result:

String Aggregate in LINQ
Note on using StringBuilder in above example: We could’ve as easily used normal sting concatenation, but since strings are immutable, this would result in performance degradation and inefficient memory use in larger data sets. Using StringBuilder is much better approach.

2 replies on “String Aggregate in LINQ”

  1. This is very elegant code. I have been struggling for days to build a non-LINQ solution to such a problem. After I finished it (tens of man-hours lost) then I find this solution. Amazing.

    How I wish I had found this post seven days ago!

  2. @Wamala Better late than never 🙂 I am glad this solution is useful to you.

Leave a Reply

Your email address will not be published. Required fields are marked *