How to GroupBy 2 values in a Generic list, VB.NET ?

Question

I'm using VB.NET on Framework 2.0. I'm looking to quickly group a Generic List<> by two properties. For the sake of this example lets say I have a List of an Order type with properties of CustomerId, ProductId, and ProductCount. How would I get the average of ProductCounts grouped by CustomerId and ProductId in VB.NET ?

Unfortunatly I cannot do this at DB level (which would have been easy). Also I cannot use LINQ or Lambada as Im on Framewwork 2.0. So I need to do this at application level in VB.net. The list is returned to me sorted by CustomerId and ProductId so I guess with a few loop sI should be able to create a average value for but there must be a cleaner way to do this?

Answer 1

This type of problem is a great example of why LINQ was introduced in C# and VB.NET. In .NET 3.5 and above, LINQ provides the "cleaner way" that you are looking for to solve this problem.

Unfortunately, because you are using .NET 2.0, you'll have solve the problem in a more or less "manual" fashion. However, you can still write clean code by encapulating the functionality you are looking for into well-defined classes and methods. This is one (but not the only) of the benefits of LINQ, i.e. that it encapsulates the functionality you expect in a clean, declarative manner.

Here's some sample code to get you started:

'The AggregateItem and AggregateItems classes will help encapsulate the '
'the functionality you are looking for.'

Public Class AggregateItem
    Public Property GroupByProperty1 As Integer ' Get/Set code...'
    Public Property GroupByProperty2 As Integer ' Get/Set code...'
    Public Property Values As List(Of Double) = New List(Of Double()() _
        ' Get/Set code...'

    Public Function GetAverage() As Double
        'Code to calculate and return Average...'
    End Function     
End Class

Public Class AggregateItems
    Public Property AggregateItemList As List(Of AggregateItem) = _
        New List(Of AggregateItem)() ' Get/Set code...'

    Public Sub InsertAggregateItem(groupByProperty1 As Integer, _
                                   groupByProperty2 As Integer, _
                                   value As Double)
        Dim aiExisting As AggregateItem
        aiExisting = GetMatchingAggregateItem(groupByProperty1, _
                                              groupByProperty2)
        If Not aiExisting Is Nothing Then
            aiExisting.Values.Add(value)
        Else
            aiExisting = New AggregateItem
            aiExisting.GroupByProperty1 = groupByProperty1
            aiExisting.GroupByProperty2 = groupByProperty2
            aiExisting.Values.Add(value)
            AggregateItemList.Add(aiExisting)
    End Sub

    Private Function GetMatchingAggregateItem(groupByProperty1 As Integer, _
                                              groupByProperty2 As Integer) _
            As AggregateItem
         Dim aiMatch As AggregateItem = Nothing
         For Each ag As AggregateItem in AggregateItemList
             If ag.GroupByProperty1 = groupByProperty1 AndAlso _
                ag.GroupByProperty2 = groupByProperty2 Then
                 aiMatch = ag
                 Exit For
             End If
         Next

         Return aiMatch
    End Function
Enc Class

'Then, to consume these classes....'

Public Module MyProgram
    Public Sub Main()
         Dim aItems As New AggregateItems()
         'Say you have List(Of Order) named listOfOrders'
         'We will loop through that list, insert the grouping IDs and values'
         'into our AggregateItems object'
         For Each o As Order In listOfOrders
            aItems.InsertAggregateItem(o.OrderId, o.ProductId, o.ProductCount)
         Next

        'Now we can loop through aItems to cleanly get the average: '
        For Each ai As AggregateItem in aItems.AggregateItemsList
            Console.WriteLine("Order: {0} Product: {1} Average: {2}", _
                ai.GroupByProperty1, ai.GroupByProperty2, _
                ai.GetAverage())
        Next
    End Sub

End Module

The nice thing about inserting your data into well-encapsulated classes is that the consuming code is very succinct and easy to understand. Also, since you already have your data aggregated into an AggregateItem class, you could easily extend that class with more methods such as GetSum() or GetMax().

Obviously, you could continue on this path of abstraction to get better re-use out of your code, but I would think that this gives you a good start.