如何:通过 LINQ 使用联接合并数据 (Visual Basic)

更新:2007 年 11 月

Visual Basic 提供 Join 和 Group Join 查询子句,使您可以基于集合之间的公共值将多个集合的内容合并在一起。这些值称为键值。熟悉关系数据库概念的开发人员会发现,实际上 Join 子句相当于 INNER JOIN,Group Join 子句相当于 LEFT OUTER JOIN。

本主题中的示例演示一些使用 Join 和 Group Join 查询子句组合数据的方式。

创建项目和添加示例数据

创建包含示例数据和类型的项目

  1. 若要运行本主题中的示例,请打开 Visual Studio,添加一个新的 Visual Basic 控制台应用程序项目。双击 Visual Basic 创建的 Module1.vb 文件。

  2. 本主题中的示例使用 Person 和 Pet 类型以及下面的代码示例中的数据。将这段代码复制到 Visual Basic 所创建的默认 Module1 模块中。

    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
    

使用 Join 子句执行内部联接

INNER JOIN 将两个集合的数据合并在一起。指定的键值相匹配的项包括在内。在另一集合中没有匹配项的所有集合项都排除在外。

在 Visual Basic 中,LINQ 提供两个选项来执行 INNER JOIN:隐式联接和显式联接。

隐式联接在 From 子句中指定要联接的集合,在 Where 子句中指定匹配键字段。Visual Basic 根据指定的键字段隐式联接两个集合。

如果要明确指出联接所用的键字段,可以使用 Join 子句指定显式联接。这种情况下,Where 子句仍可用于筛选查询结果。

使用 Join 子句执行内部联接

  • 将下面的代码添加到项目中的 Module1 模块,查看隐式和显式内部联接的示例。

    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
    

使用 Group Join 子句执行左外部联接

LEFT OUTER JOIN 包括联接左侧集合中的所有项,而对于联接的右侧集合,它仅包含其中的匹配值。联接的右侧集合中的项如果在联接的左侧集合中没有匹配项,则会排除在查询结果之外。

Group Join 子句实际上执行 LEFT OUTER JOIN。通常所称的 LEFT OUTER JOIN 与 Group Join 子句的返回结果之间的差异在于,Group Join 子句对联接的右侧集合的结果按左侧集合中的每一项进行分组。在关系数据库中,LEFT OUTER JOIN 返回未分组的结果,在该结果中,查询结果中的每一项都包含来自联接的两个集合的匹配项。这种情况下,对于联接的右侧集合中的每个匹配项,对应的左侧集合中的项都是重复的。完成下面的过程后,就可以看到这种情况。

通过扩展查询,为每个分组查询结果返回一个项,可以按照不分组结果的形式检索 Group Join 查询的结果。为此,必须确保在分组集合的 DefaultIfEmpty 方法上进行查询。这样可确保联接的左侧集合中的项包括在查询结果中,即使在右侧集合中没有匹配结果也是如此。可以向查询添加代码,以便提供一个默认值,作为联接的右侧集合中不存在匹配值时的结果。

使用 Group Join 子句执行左外部联接

  • 将下面的代码添加到项目的 Module1 模块中,查看分组左外部联接和不分组左外部联接的示例。

    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:     
    

使用复合键执行联接

在 Join 或 Group Join 子句中,可以使用 And 关键字来标识在对联接的集合中的值进行匹配时要使用的多个键字段。And 关键字指定所有指定的键字段都必须匹配要联接的项。

使用复合键执行联接

  • 将下面的代码添加到项目的 Module1 模块,查看使用复合键的联接示例。

    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
    

运行代码

添加代码以运行示例

  1. 用下面的代码替换项目中的 Module1 模块的 Sub Main,运行本主题中的示例。

    Sub Main()
      InnerJoinExample()
      LeftOuterJoinExample()
      CompositeKeyJoinExample()
    
      Console.ReadLine()
    End Sub
    
  2. 按 F5 键运行示例。

请参见

概念

Visual Basic 中的 LINQ 简介

参考

Join 子句 (Visual Basic)

Group Join 子句 (Visual Basic)

From 子句 (Visual Basic)

Where 子句 (Visual Basic)

其他资源

Visual Basic 中的 LINQ

查询 (Visual Basic)