如何：執行內部聯接（C# 編程指南） · MSDN C# 編程指南 & 參考手冊 2015

# 如何：執行內部聯接（C# 編程指南）按照關系數據庫的說法，“內部聯接”產生一個結果集，對于該結果集內第一個集合中的每個元素，只要在第二個集合中存在一個匹配元素，該元素就會出現一次。如果第一個集合中的某個元素沒有匹配元素，則它不會出現在結果集內。 [Join](https://msdn.microsoft.com/zh-cn/library/system.linq.enumerable.join.aspx) 方法（通過 C# 中的 **join** 子句調用）可實現內聯。本主題演示如何執行內部聯接的四種變體： * 簡單的內部聯接，它基于一個簡單的鍵將來自兩個數據源的元素相互關聯。 * 內部聯接，它基于一個復合鍵將來自兩個數據源的元素相互關聯。使用復合鍵（即由多個值組成的鍵）可以基于多個屬性將元素相互關聯。 * 多聯接，在其中連續的聯接操作被相互拼接在一起。 * 通過使用分組聯接實現的內部聯接。 ### 簡單鍵聯接示例下面的示例創建了兩個集合，其中分別包含以下兩個用戶定義類型的對象：Person 和 Pet。查詢使用 C# 中的 **join** 子句來將 Person 對象與 Pet 對象（其 Owner 為該 Person）進行匹配。C# 中的 **select** 子句可定義生成的對象的外觀。在此示例中，生成的對象是由主人的名字和寵物的名字組成的匿名類型。 ``` class Person { public string FirstName { get; set; } public string LastName { get; set; } } class Pet { public string Name { get; set; } public Person Owner { get; set; } } /// <summary> /// Simple inner join. /// </summary> public static void InnerJoinExample() { Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" }; Person terry = new Person { FirstName = "Terry", LastName = "Adams" }; Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" }; Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" }; Person rui = new Person { FirstName = "Rui", LastName = "Raposo" }; Pet barley = new Pet { Name = "Barley", Owner = terry }; Pet boots = new Pet { Name = "Boots", Owner = terry }; Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte }; Pet bluemoon = new Pet { Name = "Blue Moon", Owner = rui }; Pet daisy = new Pet { Name = "Daisy", Owner = magnus }; // Create two lists. List<Person> people = new List<Person> { magnus, terry, charlotte, arlene, rui }; List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy }; // Create a collection of person-pet pairs. Each element in the collection // is an anonymous type containing both the person's name and their pet's name. var query = from person in people join pet in pets on person equals pet.Owner select new { OwnerName = person.FirstName, PetName = pet.Name }; foreach (var ownerAndPet in query) { Console.WriteLine("\"{0}\" is owned by {1}", ownerAndPet.PetName, ownerAndPet.OwnerName); } } // This code produces the following output: // // "Daisy" is owned by Magnus // "Barley" is owned by Terry // "Boots" is owned by Terry // "Whiskers" is owned by Charlotte // "Blue Moon" is owned by Rui ``` 請注意，其 LastName 為“Huff”的 Person 對象未出現在結果集內，因為不存在 Pet.Owner 等于該 Person 的 Pet 對象。 ### 復合鍵聯接示例與僅僅基于一個屬性將元素相互關聯不同，使用復合鍵可基于多個屬性來比較元素。為此，需要為每個集合指定鍵選擇器函數，以便返回一個由要比較的屬性組成的匿名類型。如果給屬性加上了標簽，則這些屬性必須在每個鍵的匿名類型中都有相同的標簽，而且還必須以相同順序出現。下面的示例使用一個 Employee 對象列表和一個 Student 對象列表來確定哪些雇員同時還是學生。這兩個類型都具有 [String](https://msdn.microsoft.com/zh-cn/library/system.string.aspx) 類型的 FirstName 和 LastName 屬性。能夠從每個列表的元素創建聯接鍵的函數可返回一個由每個元素的 FirstName 和 LastName 屬性組成的匿名類型。聯接操作比較這些復合鍵是否相等，并且從每個列表中返回名字和姓氏都匹配的對象對。 ``` class Employee { public string FirstName { get; set; } public string LastName { get; set; } public int EmployeeID { get; set; } } class Student { public string FirstName { get; set; } public string LastName { get; set; } public int StudentID { get; set; } } /// <summary> /// Performs a join operation using a composite key. /// </summary> public static void CompositeKeyJoinExample() { // Create a list of employees. List<Employee> employees = new List<Employee> { new Employee { FirstName = "Terry", LastName = "Adams", EmployeeID = 522459 }, new Employee { FirstName = "Charlotte", LastName = "Weiss", EmployeeID = 204467 }, new Employee { FirstName = "Magnus", LastName = "Hedland", EmployeeID = 866200 }, new Employee { FirstName = "Vernette", LastName = "Price", EmployeeID = 437139 } }; // Create a list of students. List<Student> students = new List<Student> { new Student { FirstName = "Vernette", LastName = "Price", StudentID = 9562 }, new Student { FirstName = "Terry", LastName = "Earls", StudentID = 9870 }, new Student { FirstName = "Terry", LastName = "Adams", StudentID = 9913 } }; // Join the two data sources based on a composite key consisting of first and last name, // to determine which employees are also students. IEnumerable<string> query = from employee in employees join student in students on new { employee.FirstName, employee.LastName } equals new { student.FirstName, student.LastName } select employee.FirstName + " " + employee.LastName; Console.WriteLine("The following people are both employees and students:"); foreach (string name in query) Console.WriteLine(name); } // This code produces the following output: // // The following people are both employees and students: // Terry Adams // Vernette Price ``` ### 多聯接示例可以將任意數量的聯接操作拼接在一起以執行多聯接。C# 中的每一個 **join** 子句都可將指定的數據源與前一個聯接的結果相互關聯。下面的示例創建了三個集合：一個 Person 對象列表、一個 Cat 對象列表以及一個 Dog 對象列表。 C# 中的第一個 **join** 子句將基于匹配 Cat.Owner 的 Person 對象對主人和貓進行匹配。并返回包含 Person 對象和 Cat.Name 的匿名類型的序列。 C# 中的第二個 **join** 子句基于一個組合鍵將第一個聯接返回的匿名類型與所提供的犬列表中的 Dog 對象相互關聯，該組合鍵由類型為 Person 的 Owner 屬性和動物名字的首字母組成。該子句返回一個匿名類型序列，這些類型包含每個匹配對中的 Cat.Name 和 Dog.Name 屬性。由于這是一個內部聯接，因此僅返回第一個數據源中那些在第二個數據源中具有匹配對象的對象。 ``` class Person { public string FirstName { get; set; } public string LastName { get; set; } } class Pet { public string Name { get; set; } public Person Owner { get; set; } } class Cat : Pet { } class Dog : Pet { } public static void MultipleJoinExample() { Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" }; Person terry = new Person { FirstName = "Terry", LastName = "Adams" }; Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" }; Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" }; Person rui = new Person { FirstName = "Rui", LastName = "Raposo" }; Person phyllis = new Person { FirstName = "Phyllis", LastName = "Harris" }; Cat barley = new Cat { Name = "Barley", Owner = terry }; Cat boots = new Cat { Name = "Boots", Owner = terry }; Cat whiskers = new Cat { Name = "Whiskers", Owner = charlotte }; Cat bluemoon = new Cat { Name = "Blue Moon", Owner = rui }; Cat daisy = new Cat { Name = "Daisy", Owner = magnus }; Dog fourwheeldrive = new Dog { Name = "Four Wheel Drive", Owner = phyllis }; Dog duke = new Dog { Name = "Duke", Owner = magnus }; Dog denim = new Dog { Name = "Denim", Owner = terry }; Dog wiley = new Dog { Name = "Wiley", Owner = charlotte }; Dog snoopy = new Dog { Name = "Snoopy", Owner = rui }; Dog snickers = new Dog { Name = "Snickers", Owner = arlene }; // Create three lists. List<Person> people = new List<Person> { magnus, terry, charlotte, arlene, rui, phyllis }; List<Cat> cats = new List<Cat> { barley, boots, whiskers, bluemoon, daisy }; List<Dog> dogs = new List<Dog> { fourwheeldrive, duke, denim, wiley, snoopy, snickers }; // The first join matches Person and Cat.Owner from the list of people and // cats, based on a common Person. The second join matches dogs whose names start // with the same letter as the cats that have the same owner. var query = from person in people join cat in cats on person equals cat.Owner join dog in dogs on new { Owner = person, Letter = cat.Name.Substring(0, 1) } equals new { dog.Owner, Letter = dog.Name.Substring(0, 1) } select new { CatName = cat.Name, DogName = dog.Name }; foreach (var obj in query) { Console.WriteLine( "The cat \"{0}\" shares a house, and the first letter of their name, with \"{1}\".", obj.CatName, obj.DogName); } } // This code produces the following output: // // The cat "Daisy" shares a house, and the first letter of their name, with "Duke". // The cat "Whiskers" shares a house, and the first letter of their name, with "Wiley". ``` ### 使用分組聯接實現內部聯接的示例下面的示例演示如何使用分組聯接來實現內部聯接。在 query1 中，Person 對象列表基于與 Pet.Owner 屬性匹配的 Person 分組聯接到 Pet 對象列表。分組聯接創建了一個中間組集合，該集合中的每個組都由一個 Person 對象和匹配的 Pet 對象序列組成。通過向查詢中添加另一個 **from** 子句，此序列的序列被組合（或展平）為一個較長的序列。最終序列的元素類型由 **select** 子句指定。在此示例中，該類型是由每個匹配對的 Person.FirstName 和 Pet.Name 屬性組成的匿名類型。 query1 的結果等效于使用 **join** 子句所獲得的結果集，而沒有 **into** 子句執行內聯。 query2 變量演示了這一等效查詢。 ``` class Person { public string FirstName { get; set; } public string LastName { get; set; } } class Pet { public string Name { get; set; } public Person Owner { get; set; } } /// <summary> /// Performs an inner join by using GroupJoin(). /// </summary> public static void InnerGroupJoinExample() { Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" }; Person terry = new Person { FirstName = "Terry", LastName = "Adams" }; Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" }; Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" }; Pet barley = new Pet { Name = "Barley", Owner = terry }; Pet boots = new Pet { Name = "Boots", Owner = terry }; Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte }; Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry }; Pet daisy = new Pet { Name = "Daisy", Owner = magnus }; // Create two lists. List<Person> people = new List<Person> { magnus, terry, charlotte, arlene }; List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy }; var query1 = from person in people join pet in pets on person equals pet.Owner into gj from subpet in gj select new { OwnerName = person.FirstName, PetName = subpet.Name }; Console.WriteLine("Inner join using GroupJoin():"); foreach (var v in query1) { Console.WriteLine("{0} - {1}", v.OwnerName, v.PetName); } var query2 = from person in people join pet in pets on person equals pet.Owner select new { OwnerName = person.FirstName, PetName = pet.Name }; Console.WriteLine("\nThe equivalent operation using Join():"); foreach (var v in query2) Console.WriteLine("{0} - {1}", v.OwnerName, v.PetName); } // This code produces the following output: // // Inner join using GroupJoin(): // Magnus - Daisy // Terry - Barley // Terry - Boots // Terry - Blue Moon // Charlotte - Whiskers // // The equivalent operation using Join(): // Magnus - Daisy // Terry - Barley // Terry - Boots // Terry - Blue Moon // Charlotte - Whiskers ``` ## 編譯代碼 * 在 Visual Studio 中創建一個新的控制臺應用程序項目。 * 添加對 System.Core.dll 的引用（如果尚未引用它的話）。 * 包含 [System.Linq](https://msdn.microsoft.com/zh-cn/library/system.linq.aspx) 命名空間。 * 從示例中復制代碼，并將其粘貼到 program.cs 文件中的 Main 方法之下。向 Main 方法添加一行代碼，以調用粘入的方法。 * 運行該程序。 ## 請參閱 [Join](https://msdn.microsoft.com/zh-cn/library/system.linq.enumerable.join.aspx) [GroupJoin](https://msdn.microsoft.com/zh-cn/library/system.linq.enumerable.groupjoin.aspx) [Join Operations](https://msdn.microsoft.com/zh-cn/library/bb397908.aspx) [如何：執行分組聯接（C# 編程指南）](https://msdn.microsoft.com/zh-cn/library/bb397905.aspx) [如何：執行左外部聯接（C# 編程指南）](https://msdn.microsoft.com/zh-cn/library/bb397895.aspx) [如何：聯接兩個集合 (C#) (LINQ to XML)](https://msdn.microsoft.com/zh-cn/library/bb387080.aspx) [匿名類型（C# 編程指南）](https://msdn.microsoft.com/zh-cn/library/bb397696.aspx) [匿名類型 (Visual Basic)](https://msdn.microsoft.com/zh-cn/library/bb384767.aspx)