Share via


How to: Combine Data with LINQ by Using Joins (Visual Basic)

Visual Basic provides the Join and Group Join query clauses to enable you to combine the contents of multiple collections based on common values between the collections. These values are known as key values. Developers familiar with relational database concepts will recognize the Join clause as an INNER JOIN and the Group Join clause as, effectively, a LEFT OUTER JOIN.

The examples in this topic demonstrate a few ways to combine data by using the Join and Group Join query clauses.

Create a Project and Add Sample Data

To create a project that contains sample data and types

  1. To run the samples in this topic, open Visual Studio and add a new Visual Basic Console Application project. Double-click the Module1.vb file created by Visual Basic.

  2. The samples in this topic use the Person and Pet types and data from the following code example. Copy this code into the default Module1 module created by Visual Basic.

    Private _people As List(Of Person)
    Private _pets As List(Of Pet)
    
    Function GetPeople() As List(Of Person)
      If _people Is Nothing Then CreateLists()
      Return _people
    End Function
    
    Function GetPets(ByVal people As List(Of Person)) As List(Of Pet)
      If _pets Is Nothing Then CreateLists()
      Return _pets
    End Function
    
    Private Sub CreateLists()
      Dim pers As Person
    
      _people = New List(Of Person)
      _pets = New List(Of Pet)
    
      pers = New Person With {.FirstName = "Magnus", .LastName = "Hedlund"}
      _people.Add(pers)
      _pets.Add(New Pet With {.Name = "Daisy", .Owner = pers})
    
      pers = New Person With {.FirstName = "Terry", .LastName = "Adams"}
      _people.Add(pers)
      _pets.Add(New Pet With {.Name = "Barley", .Owner = pers})
      _pets.Add(New Pet With {.Name = "Boots", .Owner = pers})
      _pets.Add(New Pet With {.Name = "Blue Moon", .Owner = pers})
    
      pers = New Person With {.FirstName = "Charlotte", .LastName = "Weiss"}
      _people.Add(pers)
      _pets.Add(New Pet With {.Name = "Whiskers", .Owner = pers})
    
      ' Add a person with no pets for the sake of Join examples.
      _people.Add(New Person With {.FirstName = "Arlene", .LastName = "Huff"})
    
      pers = New Person With {.FirstName = "Don", .LastName = "Hall"}
      ' Do not add person to people list for the sake of Join examples.
      _pets.Add(New Pet With {.Name = "Spot", .Owner = pers})
    
      ' Add a pet with no owner for the sake of Join examples.
      _pets.Add(New Pet With {.Name = "Unknown", _
                              .Owner = New Person With {.FirstName = String.Empty, _
                                                        .LastName = String.Empty}})
    End Sub
    
    
    ...
    
    
    Class Person
      Public _firstName As String
      Public _lastName As String
    
      Public Property FirstName() As String
        Get
          Return _firstName
        End Get
        Set(ByVal value As String)
          _firstName = value
        End Set
      End Property
    
      Public Property LastName() As String
        Get
          Return _lastName
        End Get
        Set(ByVal value As String)
          _lastName = value
        End Set
      End Property
    End Class
    
    Class Pet
      Public _name As String
      Public _owner As Person
    
      Public Property Name() As String
        Get
          Return _name
        End Get
        Set(ByVal value As String)
          _name = value
        End Set
      End Property
    
      Public Property Owner() As Person
        Get
          Return _owner
        End Get
        Set(ByVal value As Person)
          _owner = value
        End Set
      End Property
    End Class
    

Perform an Inner Join by Using the Join Clause

An INNER JOIN combines data from two collections. Items for which the specified key values match are included. Any items from either collection that do not have a matching item in the other collection are excluded.

In Visual Basic, LINQ provides two options for performing an INNER JOIN: an implicit join and an explicit join.

An implicit join specifies the collections to be joined in a From clause and identifies the matching key fields in a Where clause. Visual Basic implicitly joins the two collections based on the specified key fields.

You can specify an explicit join by using the Join clause when you want to be specific about which key fields to use in the join. In this case, a Where clause can still be used to filter the query results.

To perform an Inner Join by using the Join clause

  • Add the following code to the Module1 module in your project to see examples of both an implicit and explicit inner join.

    Sub InnerJoinExample()
      ' Create two lists. 
      Dim people = GetPeople()
      Dim pets = GetPets(people)
    
      ' Implicit Join. 
      Dim petOwners = From pers In people, pet In pets _
                      Where pet.Owner Is pers _
                      Select pers.FirstName, PetName = pet.Name
    
        ' Display grouped results. 
      Dim output As New System.Text.StringBuilder
      For Each pers In petOwners
        output.AppendFormat( _
          pers.FirstName & ":" & vbTab & pers.PetName & vbCrLf)
      Next
    
      Console.WriteLine(output)
    
      ' Explicit Join. 
      Dim petOwnersJoin = From pers In people _
                          Join pet In pets _
                          On pet.Owner Equals pers _
                          Select pers.FirstName, PetName = pet.Name
    
      ' Display grouped results.
      output = New System.Text.StringBuilder()
      For Each pers In petOwnersJoin
        output.AppendFormat( _
          pers.FirstName & ":" & vbTab & pers.PetName & vbCrLf)
      Next
    
      Console.WriteLine(output)
    
      ' Both queries produce the following output: 
      ' 
      ' Magnus:    Daisy 
      ' Terry:     Barley 
      ' Terry:     Boots 
      ' Terry:     Blue Moon 
      ' Charlotte: Whiskers 
    End Sub
    

Perform a Left Outer Join by Using the Group Join Clause

A LEFT OUTER JOIN includes all the items from the left-side collection of the join and only matching values from the right-side collection of the join. Any items from the right-side collection of the join that do not have a matching item in the left-side collection are excluded from the query result.

The Group Join clause performs, in effect, a LEFT OUTER JOIN. The difference between what is typically known as a LEFT OUTER JOIN and what the Group Join clause returns is that the Group Join clause groups results from the right-side collection of the join for each item in the left-side collection. In a relational database, a LEFT OUTER JOIN returns an ungrouped result in which each item in the query result contains matching items from both collections in the join. In this case, the items from the left-side collection of the join are repeated for each matching item from the right-side collection. You will see what this looks like when you complete the next procedure.

You can retrieve the results of a Group Join query as an ungrouped result by extending your query to return an item for each grouped query result. To accomplish this, you have to ensure that you query on the DefaultIfEmpty method of the grouped collection. This ensures that items from the left-side collection of the join are still included in the query result even if they have no matching results from the right-side collection. You can add code to your query to provide a default result value when there is no matching value from the right-side collection of the join.

To perform a Left Outer Join by using the Group Join clause

  • Add the following code to the Module1 module in your project to see examples of both a grouped left outer join and an ungrouped left outer join.

    Sub LeftOuterJoinExample()
      ' Create two lists. 
      Dim people = GetPeople()
      Dim pets = GetPets(people)
    
      ' Grouped results. 
      Dim petOwnersGrouped = From pers In people _
                             Group Join pet In pets _
                               On pers Equals pet.Owner _
                             Into PetList = Group _
                             Select pers.FirstName, pers.LastName, _
                                    PetList
    
      ' Display grouped results. 
      Dim output As New System.Text.StringBuilder
      For Each pers In petOwnersGrouped
        output.AppendFormat(pers.FirstName & ":" & vbCrLf)
        For Each pt In pers.PetList
          output.AppendFormat(vbTab & pt.Name & vbCrLf)
        Next 
      Next
    
      Console.WriteLine(output)
    
      ' "Flat" results. 
      Dim petOwners = From pers In people _
                      Group Join pet In pets On pers Equals pet.Owner _
                      Into PetList = Group _
                      From pet In PetList.DefaultIfEmpty() _
                      Select pers.FirstName, pers.LastName, _
                             PetName = _
                               If(pet Is Nothing, String.Empty, pet.Name)
      ' This code produces the following output: 
      ' 
      ' Magnus: 
      '     Daisy 
      ' Terry: 
      '     Barley 
      '     Boots 
      '     Blue Moon 
      ' Charlotte: 
      '     Whiskers 
      ' Arlene: 
    
    
      ' Display "flat" results.
      output = New System.Text.StringBuilder()
      For Each pers In petOwners
        output.AppendFormat( _
          pers.FirstName & ":" & vbTab & pers.PetName & vbCrLf)
      Next
    
      Console.WriteLine(output.ToString())
    End Sub 
    
    ' This code produces the following output: 
    ' 
    ' Magnus:       Daisy 
    ' Terry:        Barley 
    ' Terry:        Boots 
    ' Terry:        Blue Moon 
    ' Charlotte:    Whiskers 
    ' Arlene:     
    

Perform a Join by Using a Composite Key

You can use the And keyword in a Join or Group Join clause to identify multiple key fields to use when matching values from the collections being joined. The And keyword specifies that all specified key fields must match for items to be joined.

To perform a Join by using a composite key

  • Add the following code to the Module1 module in your project to see examples of a join that uses a composite key.

    Sub CompositeKeyJoinExample()
      ' Create two lists. 
      Dim people = GetPeople()
      Dim pets = GetPets(people)
    
      ' Implicit Join. 
      Dim petOwners = From pers In people _
                      Join pet In pets On _
                        pet.Owner.FirstName Equals pers.FirstName _
                        And pet.Owner.LastName Equals pers.LastName _
                      Select pers.FirstName, PetName = pet.Name
    
      ' Display grouped results. 
      Dim output As New System.Text.StringBuilder
      For Each pers In petOwners
        output.AppendFormat( _
          pers.FirstName & ":" & vbTab & pers.PetName & vbCrLf)
      Next
    
      Console.WriteLine(output)
    End Sub
    

Run the Code

To add code to run the examples

  1. Replace the Sub Main in the Module1 module in your project with the following code to run the examples in this topic.

    Sub Main()
      InnerJoinExample()
      LeftOuterJoinExample()
      CompositeKeyJoinExample()
    
      Console.ReadLine()
    End Sub
    
  2. Press F5 to run the examples.

See Also

Concepts

Introduction to LINQ in Visual Basic

Data Transformations with LINQ (C#)

Reference

Join Clause (Visual Basic)

Group Join Clause (Visual Basic)

From Clause (Visual Basic)

Where Clause (Visual Basic)

Other Resources

LINQ in Visual Basic

Queries (Visual Basic)

Change History

Date

History

Reason

September 2008

Added link.

Customer feedback.