LINQ Programming: Grouping Records

	Grouping Records

Uniting the Records

If you have two sets of records of the same type, you can unite them into a new common list. This is referred to as uniting the lists. The lists can be created as an array or a List(Of ...) object. To give you the ability to unite two lists, the Enumerable class is equipped with a method named Union. Its syntax is:

Public Shared Function Union(Of TSource) ( _
    first As IEnumerable(Of TSource), _
    second As IEnumerable(Of TSource) _
) As IEnumerable(Of TSource)

The variable that calls this method and the argument must be of the same type. Here is an example:

Imports System.Linq
Imports System.Collections.Generic

Module Exercise
    Public Function Main() As Integer
        Dim Names() =
        {
            "Jerrie Sachs",
            "Stevens Souza",
            "Marianne Swanson",
            "Alain Gudmundson",
            "Jeannette Perkins",
            "Pierrette Perkins"
        }

        Dim Values() =
        {
            "Gertrude Monay",
            "Raymond Kouma"
        }

        Dim Result As IEnumerable(Of String) = Names.Union(Values)

        For Each Member In Result
            Console.WriteLine(Member)
        Next

        Console.WriteLine()
        Return 0
    End Function
End Module

This would produce:

Union

In the same way, you can unite records from lists of classes. The most important rule to observe is that both lists must have values that are of the same type.

Grouping by Categories

In all statements we have created so far, we were storing the results in a Select variable, and the Select variable was able to provide its list when necessary. We also learned how to sort the values in a Select list. As an alternative, you can ask the compiler to produce a list where the values are grouped by categories. For example, if you have a list of employees, you may want the list to be organized by gender, or by another category.

To support grouping, the LINQ provides the Group By operation. The primary formula to use it is:

Dim SubListName = From ValueHolder In List Group ValueHolder By Category

The new keywords are Group and By. They are required. The ValueHolder factor is the same variable created from the From operator. The Category is the new factor of our formula. It must be a value that can be gotten from the ValueHolder list. Here is an example:

 Dim Contractors = From StaffMembers
                  In Employees
                  Group StaffMembers By StaffMembers.Gender

The Group...By expression creates a list of categories, not a list of values, like the Select statement would do. This means that each item of the list produced by the Group...By clause is a category. Therefore, to access a category, use a For Each loop that applies to each item of the list. Each category of the Group...By list is its own list of items. Therefore, after accessing a category, inside of it, you can access an item.

Grouping Into a Variable

When you create a grouping, you get a list of categories of values and that list becomes ready to be used. In some cases, before exploring the list, you may want to perform an operation on it. One way you can do this, you can store that list in a (local) variable and use that variable as if it were a from variable.

To declare a variable to store the grouping values, you use the Into contextual keyword through the following formula:

Dim SubListName = From ValueHolder
		  In List
		  Group ValueHolder By Category Into GroupVariable ...;

The GroupVariable is the new factor in our formula. You specify it as a regular name of a variable. Here is an example:

Imports System.Linq
Imports System.Collections.Generic

Public Enum Genders
    Female
    Male
    Unknown
End Enum

Public Class Employee
    Public EmployeeNumber As Integer
    Public FirstName As String
    Public LastName As String
    Public Gender As Genders
    Public HourlySalary As Double

    Public Sub New(Optional ByVal Number As Integer = 0,
                   Optional ByVal FName As String = "John",
                   Optional ByVal LName As String = "Doe",
                   Optional ByVal Gdr As Genders = Genders.Unknown,
                   Optional ByVal salary As Double = 0D)
        EmployeeNumber = Number
        FirstName = FName
        LastName = LName
        Gender = Gdr
        HourlySalary = salary
    End Sub
End Class

Module Exercise
    Public Function Main() As Integer
        Dim Employees() As Employee =
        {
            New Employee(971974, "Patricia", "Katts", Genders.Female, 24.68),
            New Employee(408415, "Leslie", "Simms", Genders.Unknown, 15.72),
            New Employee(208411, "Raymond", "Kouma", Genders.Male, 20.15),
            New Employee(279374, "H�l�ne", "Mukoko", Genders.Female, 15.55),
            New Employee(607575, "Hermine", "Kensley", Genders.Female, 12.55),
            New Employee(707912, "Bertrand", "Yamaguchi", Genders.Male, 24.68),
            New Employee(475022, "Leslie", "Simms", Genders.Unknown, 15.72),
            New Employee(448660, "Helios", "Panko", Genders.Unknown, 12.69),
            New Employee(294800, "Peter", "Mukoko", Genders.Male, 18.85),
            New Employee(971394, "Gertrude", "Monay", Genders.Female, 20.55),
            New Employee(680404, "Lydia", "Kensley", Genders.Female, 22.58)
        }

        Dim Contractors = From StaffMembers
                          In Employees
                          Group StaffMembers By StaffMembers.Gender
                          Into People = ToArray()

        Console.WriteLine("+========+============+===========+=========+========+")
        Console.WriteLine("| Empl # | First Name | Last Name | Gender  | Salary |")
        Console.WriteLine("+========+============+===========+=========+========+")
        For Each Staff In Contractors
            For Each Employee In Staff.People
                Console.WriteLine("| {0,6} | {1,-10} | {2,-9} | {3,-7} | {4,6} |",
                                  Employee.EmployeeNumber, Employee.FirstName,
                                  Employee.LastName,
                                  Employee.Gender, Employee.HourlySalary)
                Console.WriteLine("+--------+------------+-----------+---------+--------+")
            Next
        Next


        Console.WriteLine()
        Return 0
    End Function
End Module

The Key to a Group

When you create grouping of values, the resulting list is stored in a variable of type IGrouping. The IGrouping interface is defined in the System.Linq namespace of the System.Core.dll assembly. The IGrouping interface is derived from the IEnumerable interface. This means that it gets most of its behaviors from that interface. This also means that using the IGrouping interface gives you access to the members of the Enumerable class.

The IGrouping interface is a generic class declared as follows:

Public Interface IGrouping(Of Out TKey, Out TElement) _
    Inherits IEnumerable(Of TElement), IEnumerable

In our introduction to grouping, we saw that its operation identifies the categories of items from the From variable. Each category is referred to as a key and each category can be recognized as a TKey object of the IGrouping list. This allows you to access each category. In fact, you can access a category and perform an operation on it.

Although the IGrouping interface inherits most of its functionality from the IEnumerable interface and implemented through the Enumerable class, it is equipped with only one property, named Key. To get the value of an IGrouping category, you can retrieve it from the Key property.

After creating grouping name, you can perform any operation on it inside the LINQ statement. The variable is of type IGrouping. This means that you can access its Key property or you can access one of the methods that the interface gets from IEnumerable, and then use it as you see fit.

Before ending the LINQ statement, you must create either a Group...By expression or a Select statement that uses the Into variable. Here is an example:

Imports System.Linq
Imports System.Collections.Generic

Public Enum Genders
    Female
    Male
    Unknown
End Enum

Public Class Employee
    Public EmployeeNumber As Integer
    Public FirstName As String
    Public LastName As String
    Public Gender As Genders
    Public HourlySalary As Double

    Public Sub New(Optional ByVal Number As Integer = 0,
                   Optional ByVal FName As String = "John",
                   Optional ByVal LName As String = "Doe",
                   Optional ByVal Gdr As Genders = Genders.Unknown,
                   Optional ByVal salary As Double = 0D)
        EmployeeNumber = Number
        FirstName = FName
        LastName = LName
        Gender = Gdr
        HourlySalary = salary
    End Sub
End Class

Module Exercise
    Public Function Main() As Integer
        Dim Employees() As Employee =
        {
            New Employee(971974, "Patricia", "Katts", Genders.Female, 24.68),
            New Employee(408415, "Leslie", "Simms", Genders.Unknown, 15.72),
            New Employee(208411, "Raymond", "Kouma", Genders.Male, 20.15),
            New Employee(279374, "H�l�ne", "Mukoko", Genders.Female, 15.55),
            New Employee(607575, "Hermine", "Kensley", Genders.Female, 12.55),
            New Employee(707912, "Bertrand", "Yamaguchi", Genders.Male, 24.68),
            New Employee(475022, "Leslie", "Simms", Genders.Unknown, 15.72),
            New Employee(448660, "Helios", "Panko", Genders.Unknown, 12.69),
            New Employee(294800, "Peter", "Mukoko", Genders.Male, 18.85),
            New Employee(971394, "Gertrude", "Monay", Genders.Female, 20.55),
            New Employee(680404, "Lydia", "Kensley", Genders.Female, 22.58)
        }

        Dim Contractors = From StaffMembers
                          In Employees
                          Group StaffMembers By StaffMembers.Gender
                          Into People = ToArray()
                          Where People.Contains(Employees(0))
                          Select People


        Console.WriteLine("+========+============+===========+=========+========+")
        Console.WriteLine("| Empl # | First Name | Last Name | Gender  | Salary |")
        Console.WriteLine("+========+============+===========+=========+========+")
        For Each Staff In Contractors
            For Each Employee In Staff
                Console.WriteLine("| {0,6} | {1,-10} | {2,-9} | {3,-7} | {4,6} |",
                                  Employee.EmployeeNumber, Employee.FirstName,
                                  Employee.LastName,
                                  Employee.Gender, Employee.HourlySalary)
                Console.WriteLine("+--------+------------+-----------+---------+--------+")
            Next
        Next

        Console.WriteLine()
        Return 0
    End Function
End Module

This statement, particularly the Enumerable.Contains(Employees(0)) produces only the category (group) identified as the first index (0) of the values in the main list:

Group By

Notice that all records in the final result have a common category, which in this case is the F gender of each student. For this reason, you can omit that column when presenting the values to the user. Here is an example (the column for the rating was removed from the list view):

Imports System.Linq
Imports System.Collections.Generic

Public Enum Genders
    Female
    Male
    Unknown
End Enum

Public Class Employee
    Public EmployeeNumber As Integer
    Public FirstName As String
    Public LastName As String
    Public Gender As Genders
    Public HourlySalary As Double

    Public Sub New(Optional ByVal Number As Integer = 0,
                   Optional ByVal FName As String = "John",
                   Optional ByVal LName As String = "Doe",
                   Optional ByVal Gdr As Genders = Genders.Unknown,
                   Optional ByVal salary As Double = 0D)
        EmployeeNumber = Number
        FirstName = FName
        LastName = LName
        Gender = Gdr
        HourlySalary = salary
    End Sub
End Class

Module Exercise
    Public Function Main() As Integer
        Dim Employees() As Employee =
        {
            New Employee(971974, "Patricia", "Katts", Genders.Female, 24.68),
            New Employee(408415, "Leslie", "Simms", Genders.Unknown, 15.72),
            New Employee(208411, "Raymond", "Kouma", Genders.Male, 20.15),
            New Employee(279374, "H�l�ne", "Mukoko", Genders.Female, 15.55),
            New Employee(607575, "Hermine", "Kensley", Genders.Female, 12.55),
            New Employee(707912, "Bertrand", "Yamaguchi", Genders.Male, 24.68),
            New Employee(475022, "Leslie", "Simms", Genders.Unknown, 15.72),
            New Employee(448660, "Helios", "Panko", Genders.Unknown, 12.69),
            New Employee(294800, "Peter", "Mukoko", Genders.Male, 18.85),
            New Employee(971394, "Gertrude", "Monay", Genders.Female, 20.55),
            New Employee(680404, "Lydia", "Kensley", Genders.Female, 22.58)
        }

        Dim Contractors = From StaffMembers
                          In Employees
                          Group StaffMembers By StaffMembers.Gender
                          Into People = ToArray()
                          Where People.Contains(Employees(0))
                          Select People


        Console.WriteLine("+========+============+===========+========+")
        Console.WriteLine("| Empl # | First Name | Last Name | Salary |")
        Console.WriteLine("+========+============+===========+========+")
        For Each Staff In Contractors
            For Each Employee In Staff
                Console.WriteLine("| {0,6} | {1,-10} | {2,-9} | {3,6} |",
                                  Employee.EmployeeNumber, Employee.FirstName,
                                  Employee.LastName, Employee.HourlySalary)
                Console.WriteLine("+--------+------------+-----------+--------+")
            Next
        Next


        Console.WriteLine()
        Return 0
    End Function
End Module

This would produce:

Group By

In the same, to get the category stored in the second index of the grouping, you would use Enumerable.Contains(Employees(1)). Of course this means that you can use grouping and the Into operator to get a list of items of only one particular category.

Although the GroupVariable can be Selected or Grouped...By, it cannot be used outside the LINQ statement. It is only available in the local LINQ expression.

To restrict the list of records in the result, you can add a Where condition. Here is an example:

Imports System.Linq
Imports System.Collections.Generic

Public Enum Genders
    Female
    Male
    Unknown
End Enum

Public Class Employee
    Public EmployeeNumber As Integer
    Public FirstName As String
    Public LastName As String
    Public Gender As Genders
    Public HourlySalary As Double

    Public Sub New(Optional ByVal Number As Integer = 0,
                   Optional ByVal FName As String = "John",
                   Optional ByVal LName As String = "Doe",
                   Optional ByVal Gdr As Genders = Genders.Unknown,
                   Optional ByVal salary As Double = 0D)
        EmployeeNumber = Number
        FirstName = FName
        LastName = LName
        Gender = Gdr
        HourlySalary = salary
    End Sub
End Class

Module Exercise
    Public Function Main() As Integer
        Dim Employees() As Employee =
        {
            New Employee(971974, "Patricia", "Katts", Genders.Female, 24.68),
            New Employee(408415, "Leslie", "Simms", Genders.Unknown, 15.72),
            New Employee(208411, "Raymond", "Kouma", Genders.Male, 20.15),
            New Employee(279374, "H�l�ne", "Mukoko", Genders.Female, 15.55),
            New Employee(607575, "Hermine", "Kensley", Genders.Female, 12.55),
            New Employee(707912, "Bertrand", "Yamaguchi", Genders.Male, 24.68),
            New Employee(475022, "Leslie", "Simms", Genders.Unknown, 15.72),
            New Employee(448660, "Helios", "Panko", Genders.Unknown, 12.69),
            New Employee(294800, "Peter", "Mukoko", Genders.Male, 18.85),
            New Employee(971394, "Gertrude", "Monay", Genders.Female, 20.55),
            New Employee(680404, "Lydia", "Kensley", Genders.Female, 22.58)
        }

        Dim Contractors = From StaffMembers
                          In Employees
                          Where StaffMembers.FirstName.StartsWith("H")
                          Group StaffMembers By StaffMembers.Gender
                          Into People = ToArray()
                          Select People


        Console.WriteLine("+========+============+===========+========+")
        Console.WriteLine("| Empl # | First Name | Last Name | Salary |")
        Console.WriteLine("+========+============+===========+========+")
        For Each Staff In Contractors
            For Each Employee In Staff
                Console.WriteLine("| {0,6} | {1,-10} | {2,-9} | {3,6} |",
                                  Employee.EmployeeNumber, Employee.FirstName,
                                  Employee.LastName, Employee.HourlySalary)
                Console.WriteLine("+--------+------------+-----------+--------+")
            Next
        Next


        Console.WriteLine()
        Return 0
    End Function
End Module

This would produce:

Group By