Home

Joining Records
     

Introduction
 

One of the most common operations you can perform on records consists of joining them based on a condition. Joining records is equivalent to creating a list of records from more that one set of records. In order to do this, you specify the lists, you specify a value they have in common, and you define the joining relationship.

To create a join, you must use some lists, like those we have created so far. The primary formula to join records is:

join JoinName
in ChildList
on Condition

The join condition is created before the select statement. You start with the join keyword followed by a name. The name can be used later or you can ignore it. Here is an example of starting a join:

var people = from lstTeachers
             in teachers
             join std . . .
             select Something;

The join operation is followed by the in keyword. The ChildList specifies the list created from the child class. This can be the list created by the previous in statement. Here is an example:

var people = from lstTeachers
             in teachers
             join std in students . . .
             select Something;

Of course you can write the in statement on its own line.

The Condition holds a logical expression that specifies the relationship by which the records will be joined.

To support joins, the C# language provides a context-sensitive keyword named equals. Therefore, to create a join in LINQ, the values used to establish the link in the join statement should use the object of the from statement and that of the join clause. Having those values, you can access the joining property from each object and apply the equals operator on them. The formula would be:

ChildField equals ParentField

When you create a condition, the compiler (or rather the LINQ interpreter) would check each record of the child list that has a value for the corresponding property of the parent list.

Cross Joins

A cross join is a set that includes all records from two lists as follows: the first record from the first list is associated to each record from the second list. Then the second record from the first list is associated to each record from the second list, and so on. There are two ways you create a cross join in LINQ.

For a cross join, you can use one list of numbers and another list of names. Or, you can use one list for videos and another list of music albums. You can then create a common list that includes items from each list. To create a cross join, you can use a combination of from...in... statement for each sub-list. Here is an example:

var people = from ListofStudents
             in students
             from staffMembers
             in staff
             select . . .;

Then, in the select statement, provide a way to retrieve the members from each list. This can be done by using the new operator and create a list of fields inside the curly brackets. This can be done as follows:

Imports System.Linq
Imports System.Collections.Generic

Module Exercise
    Public Function Main() As Integer
        Dim Students() As Student =
        {
            New Student(82495, "Carlton", "Blanchard"),
            New Student(20857, "Jerrie", "Sachs"),
            New Student(20935, "Charlotte", "O'Keefe"),
            New Student(79274, "Christine", "Burns"),
            New Student(79204, "Bobbie", "Swanson"),
            New Student(14815, "Marianne", "Swanson"),
            New Student(24958, "Jeannette", "Perkins"),
            New Student(24759, "Pierrette", "Perkins"),
            New Student(92804, "Charles", "Pressmann"),
            New Student(80074, "Alain", "Goodson")
        }

        Dim Teachers() As Teacher =
        {
            New Teacher("Sandrine Hanson", 16.65),
            New Teacher("Gregory Larson", 18.05),
            New Teacher("Charles Nanze", 14.95)
        }

        Dim People = From ListOfStudents
                     In Students
                     From StaffMembers
                     In Teachers
                     Select ListOfStudents, StaffMembers

        Console.WriteLine("+=======+============+===========+=================+========+")
        Console.WriteLine("| Std # | First Name | Last Name | Full Name       | Salary |")
        Console.WriteLine("+=======+============+===========+=================+========+")
        For Each Person In People
            Console.WriteLine("| {0,5} | {1,-10} | {2,-9} | {3,-15} | {4,6} |",
                              Person.ListOfStudents.StudentNumber, Person.ListOfStudents.FirstName,
                              Person.ListOfStudents.LastName, Person.StaffMembers.FullName,
                              Person.StaffMembers.HourlySalary)
            Console.WriteLine("+-------+------------+-----------+-----------------+--------+")
        Next

        Console.WriteLine()
        Return 0
    End Function
End Module

Public Class Student
    Public StudentNumber As Integer
    Public FirstName As String
    Public LastName As String

    Public Sub New(Optional ByVal Number As Integer = 0,
                   Optional ByVal FName As String = "Leslie",
                   Optional ByVal LName As String = "Doe")
        StudentNumber = number
        FirstName = FName
        LastName = LName
    End Sub
End Class

Public Class Teacher
    Public FullName As String
    Public HourlySalary As Double

    Public Sub New(Optional ByVal Name As String = "Leslie Doe",
                   Optional ByVal Salary As Double = 0D)
        FullName = Name
        HourlySalary = salary
    End Sub
End Class

This would produce:

+=======+============+===========+=================+========+
| Std # | First Name | Last Name | Full Name       | Salary |
+-------+------------+-----------+-----------------+--------+
| 82495 | Carlton    | Blanchard | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 82495 | Carlton    | Blanchard | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 82495 | Carlton    | Blanchard | Charles Nanze   |  14.95 |
+-------+------------+-----------+-----------------+--------+
| 20857 | Jerrie     | Sachs     | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 20857 | Jerrie     | Sachs     | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 20857 | Jerrie     | Sachs     | Charles Nanze   |  14.95 |
+-------+------------+-----------+-----------------+--------+
| 20935 | Charlotte  | O'Keefe   | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 20935 | Charlotte  | O'Keefe   | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 20935 | Charlotte  | O'Keefe   | Charles Nanze   |  14.95 |
+-------+------------+-----------+-----------------+--------+
| 79274 | Christine  | Burns     | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 79274 | Christine  | Burns     | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 79274 | Christine  | Burns     | Charles Nanze   |  14.95 |
+-------+------------+-----------+-----------------+--------+
| 79204 | Bobbie     | Swanson   | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 79204 | Bobbie     | Swanson   | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 79204 | Bobbie     | Swanson   | Charles Nanze   |  14.95 |
+-------+------------+-----------+-----------------+--------+
| 14815 | Marianne   | Swanson   | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 14815 | Marianne   | Swanson   | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 14815 | Marianne   | Swanson   | Charles Nanze   |  14.95 |
+-------+------------+-----------+-----------------+--------+
| 24958 | Jeannette  | Perkins   | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 24958 | Jeannette  | Perkins   | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 24958 | Jeannette  | Perkins   | Charles Nanze   |  14.95 |
+-------+------------+-----------+-----------------+--------+
| 24759 | Pierrette  | Perkins   | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 24759 | Pierrette  | Perkins   | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 24759 | Pierrette  | Perkins   | Charles Nanze   |  14.95 |
+-------+------------+-----------+-----------------+--------+
| 92804 | Charles    | Pressmann | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 92804 | Charles    | Pressmann | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 92804 | Charles    | Pressmann | Charles Nanze   |  14.95 |
+-------+------------+-----------+-----------------+--------+
| 80074 | Alain      | Goodson   | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 80074 | Alain      | Goodson   | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 80074 | Alain      | Goodson   | Charles Nanze   |  14.95 |
+=======+============+===========+=================+========+

Press any key to continue . . .

Notice that each record from one list is associated to each record of the other list. To put a restriction in the list of values, you can add a Where condition. Here is an example:

Dim People = From ListOfStudents
             In Students
             From StaffMembers
             In Teachers
             Where ListOfStudents.FirstName.StartsWith("C")
             Select ListOfStudents, StaffMembers

This would produce:

+=======+============+===========+=================+========+
| Std # | First Name | Last Name | Full Name       | Salary |
+-------+------------+-----------+-----------------+--------+
| 82495 | Carlton    | Blanchard | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 82495 | Carlton    | Blanchard | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 82495 | Carlton    | Blanchard | Charles Nanze   |  14.95 |
+-------+------------+-----------+-----------------+--------+
| 20935 | Charlotte  | O'Keefe   | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 20935 | Charlotte  | O'Keefe   | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 20935 | Charlotte  | O'Keefe   | Charles Nanze   |  14.95 |
+-------+------------+-----------+-----------------+--------+
| 79274 | Christine  | Burns     | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 79274 | Christine  | Burns     | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 79274 | Christine  | Burns     | Charles Nanze   |  14.95 |
+-------+------------+-----------+-----------------+--------+
| 92804 | Charles    | Pressmann | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 92804 | Charles    | Pressmann | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 92804 | Charles    | Pressmann | Charles Nanze   |  14.95 |
+=======+============+===========+=================+========+

Press any key to continue . . .

In the same way, you can create a Where statement for each From list. Here is an example:

Dim People = From ListOfStudents
             In Students
             From StaffMembers
             In Teachers
             Where ListOfStudents.FirstName.StartsWith("C")
             Where StaffMembers.HourlySalary > 15.0
             Select ListOfStudents, StaffMembers

This can also be written as follows:

Dim People = From ListOfStudents
                     In Students
                     Where ListOfStudents.FirstName.StartsWith("C")
                     From StaffMembers
                     In Teachers
                     Where StaffMembers.HourlySalary > 15.0
                     Select ListOfStudents, StaffMembers

This would produce:

+=======+============+===========+=================+========+
| Std # | First Name | Last Name | Full Name       | Salary |
+-------+------------+-----------+-----------------+--------+
| 82495 | Carlton    | Blanchard | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 82495 | Carlton    | Blanchard | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 20935 | Charlotte  | O'Keefe   | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 20935 | Charlotte  | O'Keefe   | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 79274 | Christine  | Burns     | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 79274 | Christine  | Burns     | Gregory Larson  |  18.05 |
+-------+------------+-----------+-----------------+--------+
| 92804 | Charles    | Pressmann | Sandrine Hanson |  16.65 |
+-------+------------+-----------+-----------------+--------+
| 92804 | Charles    | Pressmann | Gregory Larson  |  18.05 |
+=======+============+===========+=================+========+

Press any key to continue . . .

The second technique of creating a cross join involves two lists that have a relationship. You start with a class that can be used as the basis of a list. Before creating the join, you should (must) have a member from each class so that these two members will be used to join the lists. These members should hold unique values. In the above class, that would be the role of the TeacherID property, where we would make sure that each record has a unique TeacherID value.

When creating the other class, you should (must) create a property (or field) that represents the unique-value provider of the first class. In our example, when creating a class that will be used to create a list of students, we must create a property (or field) that represents the TeacherID of the first class. The class can be created as follows:

Public Class Student
    Public StudentNumber As Integer
    Public TeacherID As Integer
    Public FirstName As String
    Public LastName As String

    Public Sub New(Optional ByVal Number As Integer = 0,
                   Optional ByVal teachID As Integer = 1,
                   Optional ByVal FName As String = "Leslie",
                   Optional ByVal LName As String = "Doe")
        StudentNumber = Number
        TeacherID = teachID
        FirstName = FName
        LastName = LName
    End Sub
End Class

To create a cross join, apply the parentheses to the From and the Join statements to indicate the index by which the records would be established (you can use the parentheses if the lists were created as arrays or as List(Of...)). Here is an example of creating a cross join:

Imports System.Linq
Imports System.Collections.Generic

Module Exercise
    Public Function Main() As Integer
        Dim Students() As Student =
        {
            New Student(82495, 2, "Carlton", "Blanchard"),
            New Student(20857, 2, "Jerrie", "Sachs"),
            New Student(20935, 1, "Charlotte", "O'Keefe"),
            New Student(79274, 3, "Christine", "Burns"),
            New Student(79204, 2, "Bobbie", "Swanson"),
            New Student(14815, 1, "Marianne", "Swanson"),
            New Student(24958, 3, "Jeannette", "Perkins"),
            New Student(24759, 3, "Pierrette", "Perkins"),
            New Student(92804, 3, "Charles", "Pressmann"),
            New Student(80074, 2, "Alain", "Goodson")
        }

        Dim Teachers() As Teacher =
        {
            New Teacher(1, "Sandrine Hanson", 16.65),
            New Teacher(2, "Gregory Larson", 18.05),
            New Teacher(3, "Charles Nanze", 14.95)
        }

        Dim People = From ListOfTeachers
                     In Teachers
                     From Pupils
                     In Students
                     Join std In Students On ListOfTeachers.TeacherID Equals std.TeacherID

        Console.WriteLine("+=======+============+===========+=================+")
        Console.WriteLine("| Std # | First Name | Last Name | Teacher Name    |")
        For Each Person In People
            Console.WriteLine("+-------+------------+-----------+-----------------+")
            Console.WriteLine("| {0,5} | {1,-10} | {2,-9} | {3,-15} |",
                              Person.Pupils.StudentNumber, Person.Pupils.FirstName,
                              Person.Pupils.LastName, Person.ListOfTeachers.FullName)
        Next
        Console.WriteLine("+=======+============+===========+=================+========+")

        Console.WriteLine()
        Return 0
    End Function
End Module

Public Class Student
    Public StudentNumber As Integer
    Public TeacherID As Integer
    Public FirstName As String
    Public LastName As String

    Public Sub New(Optional ByVal Number As Integer = 0,
                   Optional ByVal TID As Integer = 1,
                   Optional ByVal FName As String = "Leslie",
                   Optional ByVal LName As String = "Doe")
        StudentNumber = Number
        TeacherID = TID
        FirstName = FName
        LastName = LName
    End Sub
End Class

Public Class Teacher
    Public TeacherID As Integer
    Public FullName As String
    Public HourlySalary As Double

    Public Sub New(Optional ByVal TID As Integer = 1,
                   Optional ByVal Name As String = "Leslie Doe",
                   Optional ByVal Salary As Double = 0D)
        TeacherID = TID
        FullName = Name
        HourlySalary = Salary
    End Sub
End Class

This would produce:

+=======+============+===========+=================+
| Std # | First Name | Last Name | Teacher Name    |
+-------+------------+-----------+-----------------+
| 82495 | Carlton    | Blanchard | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 82495 | Carlton    | Blanchard | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 20857 | Jerrie     | Sachs     | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 20857 | Jerrie     | Sachs     | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 20935 | Charlotte  | O'Keefe   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 20935 | Charlotte  | O'Keefe   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 79274 | Christine  | Burns     | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 79274 | Christine  | Burns     | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 79204 | Bobbie     | Swanson   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 79204 | Bobbie     | Swanson   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 14815 | Marianne   | Swanson   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 14815 | Marianne   | Swanson   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 24958 | Jeannette  | Perkins   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 24958 | Jeannette  | Perkins   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 24759 | Pierrette  | Perkins   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 24759 | Pierrette  | Perkins   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 92804 | Charles    | Pressmann | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 92804 | Charles    | Pressmann | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 80074 | Alain      | Goodson   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 80074 | Alain      | Goodson   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 82495 | Carlton    | Blanchard | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 82495 | Carlton    | Blanchard | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 82495 | Carlton    | Blanchard | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 82495 | Carlton    | Blanchard | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 20857 | Jerrie     | Sachs     | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 20857 | Jerrie     | Sachs     | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 20857 | Jerrie     | Sachs     | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 20857 | Jerrie     | Sachs     | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 20935 | Charlotte  | O'Keefe   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 20935 | Charlotte  | O'Keefe   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 20935 | Charlotte  | O'Keefe   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 20935 | Charlotte  | O'Keefe   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 79274 | Christine  | Burns     | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 79274 | Christine  | Burns     | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 79274 | Christine  | Burns     | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 79274 | Christine  | Burns     | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 79204 | Bobbie     | Swanson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 79204 | Bobbie     | Swanson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 79204 | Bobbie     | Swanson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 79204 | Bobbie     | Swanson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 14815 | Marianne   | Swanson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 14815 | Marianne   | Swanson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 14815 | Marianne   | Swanson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 14815 | Marianne   | Swanson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 24958 | Jeannette  | Perkins   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 24958 | Jeannette  | Perkins   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 24958 | Jeannette  | Perkins   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 24958 | Jeannette  | Perkins   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 24759 | Pierrette  | Perkins   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 24759 | Pierrette  | Perkins   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 24759 | Pierrette  | Perkins   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 24759 | Pierrette  | Perkins   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 92804 | Charles    | Pressmann | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 92804 | Charles    | Pressmann | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 92804 | Charles    | Pressmann | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 92804 | Charles    | Pressmann | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 80074 | Alain      | Goodson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 80074 | Alain      | Goodson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 80074 | Alain      | Goodson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 80074 | Alain      | Goodson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 82495 | Carlton    | Blanchard | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 82495 | Carlton    | Blanchard | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 82495 | Carlton    | Blanchard | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 82495 | Carlton    | Blanchard | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 20857 | Jerrie     | Sachs     | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 20857 | Jerrie     | Sachs     | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 20857 | Jerrie     | Sachs     | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 20857 | Jerrie     | Sachs     | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 20935 | Charlotte  | O'Keefe   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 20935 | Charlotte  | O'Keefe   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 20935 | Charlotte  | O'Keefe   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 20935 | Charlotte  | O'Keefe   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 79274 | Christine  | Burns     | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 79274 | Christine  | Burns     | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 79274 | Christine  | Burns     | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 79274 | Christine  | Burns     | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 79204 | Bobbie     | Swanson   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 79204 | Bobbie     | Swanson   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 79204 | Bobbie     | Swanson   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 79204 | Bobbie     | Swanson   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 14815 | Marianne   | Swanson   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 14815 | Marianne   | Swanson   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 14815 | Marianne   | Swanson   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 14815 | Marianne   | Swanson   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 24958 | Jeannette  | Perkins   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 24958 | Jeannette  | Perkins   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 24958 | Jeannette  | Perkins   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 24958 | Jeannette  | Perkins   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 24759 | Pierrette  | Perkins   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 24759 | Pierrette  | Perkins   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 24759 | Pierrette  | Perkins   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 24759 | Pierrette  | Perkins   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 92804 | Charles    | Pressmann | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 92804 | Charles    | Pressmann | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 92804 | Charles    | Pressmann | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 92804 | Charles    | Pressmann | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 80074 | Alain      | Goodson   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 80074 | Alain      | Goodson   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 80074 | Alain      | Goodson   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 80074 | Alain      | Goodson   | Charles Nanze   |
+=======+============+===========+=================+========+

Press any key to continue . . .

Notice that each record of the parent class (the teacher) is associted to each record of the child class (the student).
 
 
 

Inner Joins

You may have a list where some records don't have a value for some fields. You can make your final list include only records that have a value for the child. This is called an inner join.

To create an inner join, start with a normal join as done for a cross join. For the join formula, instead of joining the fields using an index, access the key from the value of the Join clause and assign it to the corresponding key from the value of the From statement. Here is an example:

Imports System.Linq
Imports System.Collections.Generic

Module Exercise
    Public Function Main() As Integer
        Dim Students() As Student =
        {
            New Student(82495, 2, "Carlton", "Blanchard"),
            New Student(20857, 2, "Jerrie", "Sachs"),
            New Student(20935, 1, "Charlotte", "O'Keefe"),
            New Student(79274, 3, "Christine", "Burns"),
            New Student(79204, 2, "Bobbie", "Swanson"),
            New Student(14815, 1, "Marianne", "Swanson"),
            New Student(24958, 3, "Jeannette", "Perkins"),
            New Student(24759, 3, "Pierrette", "Perkins"),
            New Student(92804, 3, "Charles", "Pressmann"),
            New Student(80074, 2, "Alain", "Goodson")
        }

        Dim Teachers() As Teacher =
        {
            New Teacher(1, "Sandrine Hanson", 16.65),
            New Teacher(2, "Gregory Larson", 18.05),
            New Teacher(3, "Charles Nanze", 14.95)
        }

        Dim People = From ListOfTeachers
                     In Teachers
                     Join std In Students On ListOfTeachers.TeacherID Equals std.TeacherID

        Console.WriteLine("+=======+============+===========+=================+")
        Console.WriteLine("| Std # | First Name | Last Name | Teacher Name    |")
        Console.WriteLine("+=======+============+===========+=================+")
        For Each Person In People
            Console.WriteLine("| {0,5} | {1,-10} | {2,-9} | {3,-15} |",
                              Person.std.StudentNumber, Person.std.FirstName, Person.std.LastName, Person.ListOfTeachers.FullName)
            Console.WriteLine("+-------+------------+-----------+-----------------+")
        Next

        Console.WriteLine()
        Return 0
    End Function
End Module

Public Class Student
    Public StudentNumber As Integer
    Public TeacherID As Integer
    Public FirstName As String
    Public LastName As String

    Public Sub New(Optional ByVal Number As Integer = 0,
                   Optional ByVal teachID As Integer = 1,
                   Optional ByVal FName As String = "Leslie",
                   Optional ByVal LName As String = "Doe")
        StudentNumber = Number
        TeacherID = teachID
        FirstName = FName
        LastName = LName
    End Sub
End Class

Public Class Teacher
    Public TeacherID As Integer
    Public FullName As String
    Public HourlySalary As Double

    Public Sub New(Optional ByVal ID As Integer = 1,
                   Optional ByVal Name As String = "Leslie Doe",
                   Optional ByVal Salary As Double = 0D)
        TeacherID = ID
        FullName = Name
        HourlySalary = Salary
    End Sub
End Class

This would produce:

+=======+============+===========+=================+
| Std # | First Name | Last Name | Teacher Name    |
+=======+============+===========+=================+
| 20935 | Charlotte  | O'Keefe   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 14815 | Marianne   | Swanson   | Sandrine Hanson |
+-------+------------+-----------+-----------------+
| 82495 | Carlton    | Blanchard | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 20857 | Jerrie     | Sachs     | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 79204 | Bobbie     | Swanson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 80074 | Alain      | Goodson   | Gregory Larson  |
+-------+------------+-----------+-----------------+
| 79274 | Christine  | Burns     | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 24958 | Jeannette  | Perkins   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 24759 | Pierrette  | Perkins   | Charles Nanze   |
+-------+------------+-----------+-----------------+
| 92804 | Charles    | Pressmann | Charles Nanze   |
+-------+------------+-----------+-----------------+

Press any key to continue . . .

Outer Joins

Instead of showing only records that have entries in the child list, you may want your resulting list to include all records, including those that  have a 0 value or are empty. To get this, you would create an outer join.

To suport outer joins, the Enumerable class is equipped with a method named DefaultIfEmpty. Its syntax is:

Public Shared Function DefaultIfEmpty(Of TSource) ( _
    source As IEnumerable(Of TSource) _
) As IEnumerable(Of TSource)

The source is the list that calls this method. To restrict the result, you can add a Where condition to a join.
 
 
   
 
Previous Copyright © 2010-2016, FunctionX Home