I’ve described before how to group and aggregate data in ADO.NET data table using standard .NET 2.0 features. But if you happen to use .NET 3.5 or above and Visual Studio 2008 or above – that entire block of code can be replaced with a single LINQ query:

Function GroupBy(ByVal i_sGroupByColumn As String,  ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable

     dim aQuery = From row In i_dSourceTable Group By Group1 = row(i_sGroupByColumn) Into Group Select Group1,  Aggr = Group.Count(Function(row) row(i_sAggregateColumn))

    return aQuery.toDataTable

End Function

The query at line 3 is a LINQ to DataSet query, so reference to System.Data.Linq has to be added to your project. The Group.Count aggregate can be replaced with Group.Max, Group.Min, Group.Sum or Group.Avg to perform respectful function.

There is one caveat though. If you need to return grouped result as a data table, a .toDataTable method needs to be used on query results. Unfortunately this method exists only if your query is of a type IEnumerable(of DataRow) in other words returns datarows. The query above returns anonymous type. So there is no ready-to-use .toDataTable method.

Fortunately the method can be added. Download following 2 code modules (borrowed from the code accompanied “LINQ in Action book):

ObjectShreder.vb
DataSetLinqOperators.vb

Add them to your project. Bingo! Your query of a type IEnumerable(of <anonymous type>) now has .toDataTable method.

Another possibility (if you don’t want to use some external 3-rd party code) is to build a table the standard way creating and filling table rows while looping thru query results.