HOW TO：執行內部聯結 (C# 程式設計手冊)
更新：2007 年 11 月
在關聯式資料庫詞彙中，「內部聯結」(Inner Join) 會產生結果集，其中第一個集合的每個項目會針對第二個集合中的每個相符項目顯示一次。如果第一個集合中的項目沒有相符項目，就不會顯示在結果集中。Join 方法 (由 C# 中的 join 子句或 Visual Basic 中的 Join 子句呼叫) 會實作內部聯結。
本主題會顯示如何執行四種不同的內部聯結：
根據簡單索引鍵，使兩個資料來源的項目相互關聯的簡單內部聯結。
根據「複合」(Composite) 索引鍵，使兩個資料來源的項目相互關聯的內部聯結。複合索引鍵是由一個以上的值組成的索引鍵，可以讓您根據一個以上的屬性相互關聯各個項目。
「多重聯結」(Multiple Join)，在其中後續的聯結作業會互相附加。
使用群組聯結實作的內部聯結。
範例
簡單索引鍵聯結範例
下列範例建立兩個集合，包含兩個使用者定義型別 (Person 和 Pet) 的物件。查詢使用 C# 中的 join 子句或 Visual Basic 中的 Join 子句，比對 Person 物件與其 Owner 為 Person 的 Pet 物件。C# 中的 select 子句或 Visual Basic 中的 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 物件的 Pet.Owner 等於 Person。
複合索引鍵聯結範例
並非只根據一個屬性使項目相互關聯，而是可以使用複合索引鍵根據多個屬性比較項目。若要這麼做，請針對每個集合指定索引鍵選取器函式以傳回匿名型別，該型別是由您要比較的屬性組成。如果您標示屬性，屬性在每個索引鍵匿名型別中必須有相同的標籤。屬性也必須以相同順序顯示。
下列範例使用 Employee 物件清單和 Student 物件清單，判斷哪位員工同時也是學生。這些型別都有型別為 String 的 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 子句或 Visual Basic 中的 Join 子句，會使指定資料來源與先前聯結的結果相互關聯。
下列範例建立三個集合：Person 物件清單、Cat 物件清單及 Dog 物件清單。
C# 中的第一個 join 子句或 Visual Basic 中的 Join 子句，會根據符合 Cat.Owner 的 Person 物件比對人和貓。傳回由 Person 物件和 Cat.Name 組成之匿名型別的序列。
C# 中的第二個 join 子句或 Visual Basic 中的 Join 子句，會根據由型別 Person 的 Owner 屬性和動物名稱的第一個字母組成的複合索引鍵，使第一個聯結傳回之匿名型別與狗清單中提供的 Dog 物件相互關聯。傳回包含每個相符配對之 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 子句 (Visual Basic 中的 From 子句) 加入至查詢，此序列會結合 (或簡維) 至更長的序列。最後序列之項目的型別是由 select 子句 (Visual Basic 中的 Select 子句) 指定。在這個範例中，該型別是匿名型別，由每個相符配對的 Person.FirstName 和 Pet.Name 屬性組成。
query1 的結果等同於使用 join 子句而不使用 C# 中的 into 子句或 Visual Basic 中的 Join 子句執行內部聯結，所取得的結果集。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 命名空間。
從範例複製程式碼，並將其貼入 program.cs 檔中 Main 方法的下方。將一行程式碼加入至 Main 方法，以呼叫您所貼上的方法。
執行程式。