join 子句(C# 参考)join clause (C# Reference)

join 子句可用于将来自不同源序列并且在对象模型中没有直接关系的元素相关联。The join clause is useful for associating elements from different source sequences that have no direct relationship in the object model. 唯一的要求是每个源中的元素需要共享某个可以进行比较以判断是否相等的值。The only requirement is that the elements in each source share some value that can be compared for equality. 例如,食品经销商可能拥有某种产品的供应商列表以及买主列表。For example, a food distributor might have a list of suppliers of a certain product, and a list of buyers. 例如,可以使用 join 子句创建该产品同一指定地区供应商和买主的列表。A join clause can be used, for example, to create a list of the suppliers and buyers of that product who are all in the same specified region.

join 子句将 2 个源序列作为输入。A join clause takes two source sequences as input. 每个序列中的元素都必须是可以与其他序列中的相应属性进行比较的属性,或者包含一个这样的属性。The elements in each sequence must either be or contain a property that can be compared to a corresponding property in the other sequence. join 子句使用特殊 equals 关键字比较指定的键是否相等。The join clause compares the specified keys for equality by using the special equals keyword. join 子句执行的所有联接都是同等联接。All joins performed by the join clause are equijoins. join 子句的输出形式取决于执行的联接的具体类型。The shape of the output of a join clause depends on the specific type of join you are performing. 以下是 3 种最常见的联接类型:The following are three most common join types:

  • 内部联接Inner join

  • 分组联接Group join

  • 左外部联接Left outer join

内部联接Inner join

以下示例演示了一个简单的内部同等联接。The following example shows a simple inner equijoin. 此查询生成一个“产品名称/类别”对平面序列。This query produces a flat sequence of "product name / category" pairs. 同一类别字符串将出现在多个元素中。The same category string will appear in multiple elements. 如果 categories 中的某个元素不具有匹配的 products,则该类别不会出现在结果中。If an element from categories has no matching products, that category will not appear in the results.

var innerJoinQuery =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID
    select new { ProductName = prod.Name, Category = category.Name }; //produces flat sequence

有关详细信息,请参阅执行内联For more information, see Perform inner joins.

分组联接Group join

含有 into 表达式的 join 子句称为分组联接。A join clause with an into expression is called a group join.

var innerGroupJoinQuery =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    select new { CategoryName = category.Name, Products = prodGroup };

分组联接会生成分层的结果序列,该序列将左侧源序列中的元素与右侧源序列中的一个或多个匹配元素相关联。A group join produces a hierarchical result sequence, which associates elements in the left source sequence with one or more matching elements in the right side source sequence. 分组联接没有等效的关系术语;它本质上是一个对象数组序列。A group join has no equivalent in relational terms; it is essentially a sequence of object arrays.

如果在右侧源序列中找不到与左侧源中的元素相匹配的元素,则 join 子句会为该项生成一个空数组。If no elements from the right source sequence are found to match an element in the left source, the join clause will produce an empty array for that item. 因此,分组联接基本上仍然是一种内部同等联接,区别在于分组联接将结果序列组织为多个组。Therefore, the group join is still basically an inner-equijoin except that the result sequence is organized into groups.

如果只选择分组联接的结果,则可访问各项,但无法识别结果所匹配的项。If you just select the results of a group join, you can access the items, but you cannot identify the key that they match on. 因此,通常更为有用的做法是:选择分组联接的结果并将其放入一个也包含该项名的新类型中,如上例所示。Therefore, it is generally more useful to select the results of the group join into a new type that also has the key name, as shown in the previous example.

当然,还可以将分组联接的结果用作其他子查询的生成器:You can also, of course, use the result of a group join as the generator of another subquery:

var innerGroupJoinQuery2 =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    from prod2 in prodGroup
    where prod2.UnitPrice > 2.50M
    select prod2;

有关详细信息,请参阅执行分组联接For more information, see Perform grouped joins.

左外部联接Left outer join

在左外部联接中,将返回左侧源序列中的所有元素,即使右侧序列中没有其匹配元素也是如此。In a left outer join, all the elements in the left source sequence are returned, even if no matching elements are in the right sequence. 若要在 LINQ 中执行左外部联接,请结合使用 DefaultIfEmpty 方法与分组联接,指定要在某个左侧元素不具有匹配元素时生成的默认右侧元素。To perform a left outer join in LINQ, use the DefaultIfEmpty method in combination with a group join to specify a default right-side element to produce if a left-side element has no matches. 可以使用 null 作为任何引用类型的默认值,也可以指定用户定义的默认类型。You can use null as the default value for any reference type, or you can specify a user-defined default type. 以下示例演示了用户定义的默认类型:In the following example, a user-defined default type is shown:

var leftOuterJoinQuery =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    from item in prodGroup.DefaultIfEmpty(new Product { Name = String.Empty, CategoryID = 0 })
    select new { CatName = category.Name, ProdName = item.Name };

有关详细信息,请参阅执行左外部联接For more information, see Perform left outer joins.

等于运算符The equals operator

join 子句执行同等联接。A join clause performs an equijoin. 换言之,只能基于 2 个项之间的相等关系进行匹配。In other words, you can only base matches on the equality of two keys. 不支持其他类型的比较,例如“大于”或“不等于”。Other types of comparisons such as "greater than" or "not equals" are not supported. 为了表明所有联接都是同等联接,join 子句使用 equals 关键字而不是 == 运算符。To make clear that all joins are equijoins, the join clause uses the equals keyword instead of the == operator. equals 关键字只能在 join 子句中使用,并且其与 == 运算符之间存在一个重要差别。The equals keyword can only be used in a join clause and it differs from the == operator in one important way. 对于 equals,左键使用外部源序列,而右键使用内部源序列。With equals, the left key consumes the outer source sequence, and the right key consumes the inner source. 外部源仅在 equals 的左侧位于范围内,而内部源序列仅在其右侧位于范围内。The outer source is only in scope on the left side of equals and the inner source sequence is only in scope on the right side.

非同等联接Non-equijoins

通过使用多个 from 子句将新序列单独引入查询,可以执行非同等联接、交叉联接和其他自定义联接操作。You can perform non-equijoins, cross joins, and other custom join operations by using multiple from clauses to introduce new sequences independently into a query. 有关详细信息,请参阅执行自定义联接操作For more information, see Perform custom join operations.

对象集合联接与关系表Joins on object collections vs. relational tables

在 LINQ 查询表达式中,联接操作是在对象集合上执行的。In a LINQ query expression, join operations are performed on object collections. 不能使用与 2 个关系表完全相同的方式“联接”对象集合。Object collections cannot be "joined" in exactly the same way as two relational tables. 在 LINQ 中,仅当 2 个源序列没有通过任何关系相互联系时,才需要使用显式 join 子句。In LINQ, explicit join clauses are only required when two source sequences are not tied by any relationship. 使用 LINQ to SQLLINQ to SQL 时,外键表在对象模型中表示为主表的属性。When working with LINQ to SQLLINQ to SQL, foreign key tables are represented in the object model as properties of the primary table. 例如,在 Northwind 数据库中,Customer 表与 Orders 表之间具有外键关系。For example, in the Northwind database, the Customer table has a foreign key relationship with the Orders table. 将这两个表映射到对象模型时,Customer 类具有一个 Orders 属性,其中包含与该 Customer 相关联的 Orders 集合。When you map the tables to the object model, the Customer class has an Orders property that contains the collection of Orders associated with that Customer. 实际上,已经为你执行了联接。In effect, the join has already been done for you.

若要详细了解如何在 LINQ to SQLLINQ to SQL 的上下文中跨相关表执行查询,请参阅操作说明:映射数据库关系For more information about querying across related tables in the context of LINQ to SQLLINQ to SQL, see How to: Map Database Relationships.

组合键Composite keys

可通过使用组合键测试多个值是否相等。You can test for equality of multiple values by using a composite key. 有关详细信息,请参阅使用组合键进行联接For more information, see Join by using composite keys. 还可以在 group 子句中使用组合键。Composite keys can be also used in a group clause.

示例Example

以下示例比较了使用相同的匹配键对相同数据源执行内部联接、分组联接和左外部联接的结果。The following example compares the results of an inner join, a group join, and a left outer join on the same data sources by using the same matching keys. 这些示例中添加了一些额外的代码,以便在控制台显示中阐明结果。Some extra code is added to these examples to clarify the results in the console display.

   class JoinDemonstration
   {
       #region Data

       class Product
       {
           public string Name { get; set; }
           public int CategoryID { get; set; }
       }

       class Category
       {
           public string Name { get; set; }
           public int ID { get; set; }
       }

       // Specify the first data source.
       List<Category> categories = new List<Category>()
       { 
           new Category {Name="Beverages", ID=001},
           new Category {Name="Condiments", ID=002},
           new Category {Name="Vegetables", ID=003},
           new Category {Name="Grains", ID=004},
           new Category {Name="Fruit", ID=005}            
       };

       // Specify the second data source.
       List<Product> products = new List<Product>()
      {
         new Product {Name="Cola",  CategoryID=001},
         new Product {Name="Tea",  CategoryID=001},
         new Product {Name="Mustard", CategoryID=002},
         new Product {Name="Pickles", CategoryID=002},
         new Product {Name="Carrots", CategoryID=003},
         new Product {Name="Bok Choy", CategoryID=003},
         new Product {Name="Peaches", CategoryID=005},
         new Product {Name="Melons", CategoryID=005},
       };
       #endregion

       static void Main(string[] args)
       {
           JoinDemonstration app = new JoinDemonstration();

           app.InnerJoin();
           app.GroupJoin();
           app.GroupInnerJoin();
           app.GroupJoin3();
           app.LeftOuterJoin();
           app.LeftOuterJoin2();

           // Keep the console window open in debug mode.
           Console.WriteLine("Press any key to exit.");
           Console.ReadKey();
       }

       void InnerJoin()
       {
           // Create the query that selects 
           // a property from each element.
           var innerJoinQuery =
              from category in categories
              join prod in products on category.ID equals prod.CategoryID
              select new { Category = category.ID, Product = prod.Name };

           Console.WriteLine("InnerJoin:");
           // Execute the query. Access results 
           // with a simple foreach statement.
           foreach (var item in innerJoinQuery)
           {
               Console.WriteLine("{0,-10}{1}", item.Product, item.Category);
           }
           Console.WriteLine("InnerJoin: {0} items in 1 group.", innerJoinQuery.Count());
           Console.WriteLine(System.Environment.NewLine);
       }

       void GroupJoin()
       {
           // This is a demonstration query to show the output
           // of a "raw" group join. A more typical group join
           // is shown in the GroupInnerJoin method.
           var groupJoinQuery =
              from category in categories
              join prod in products on category.ID equals prod.CategoryID into prodGroup
              select prodGroup;

           // Store the count of total items (for demonstration only).
           int totalItems = 0;

           Console.WriteLine("Simple GroupJoin:");

           // A nested foreach statement is required to access group items.
           foreach (var prodGrouping in groupJoinQuery)
           {
               Console.WriteLine("Group:");
               foreach (var item in prodGrouping)
               {
                   totalItems++;
                   Console.WriteLine("   {0,-10}{1}", item.Name, item.CategoryID);
               }
           }
           Console.WriteLine("Unshaped GroupJoin: {0} items in {1} unnamed groups", totalItems, groupJoinQuery.Count());
           Console.WriteLine(System.Environment.NewLine);
       }

       void GroupInnerJoin()
       {
           var groupJoinQuery2 =
               from category in categories
               orderby category.ID
               join prod in products on category.ID equals prod.CategoryID into prodGroup
               select new
               {
                   Category = category.Name,
                   Products = from prod2 in prodGroup
                              orderby prod2.Name
                              select prod2
               };

           //Console.WriteLine("GroupInnerJoin:");
           int totalItems = 0;

           Console.WriteLine("GroupInnerJoin:");
           foreach (var productGroup in groupJoinQuery2)
           {
               Console.WriteLine(productGroup.Category);
               foreach (var prodItem in productGroup.Products)
               {
                   totalItems++;
                   Console.WriteLine("  {0,-10} {1}", prodItem.Name, prodItem.CategoryID);
               }
           }
           Console.WriteLine("GroupInnerJoin: {0} items in {1} named groups", totalItems, groupJoinQuery2.Count());
           Console.WriteLine(System.Environment.NewLine);
       }

       void GroupJoin3()
       {

           var groupJoinQuery3 =
               from category in categories
               join product in products on category.ID equals product.CategoryID into prodGroup
               from prod in prodGroup
               orderby prod.CategoryID
               select new { Category = prod.CategoryID, ProductName = prod.Name };

           //Console.WriteLine("GroupInnerJoin:");
           int totalItems = 0;

           Console.WriteLine("GroupJoin3:");
           foreach (var item in groupJoinQuery3)
           {
               totalItems++;
               Console.WriteLine("   {0}:{1}", item.ProductName, item.Category);
           }

           Console.WriteLine("GroupJoin3: {0} items in 1 group", totalItems);
           Console.WriteLine(System.Environment.NewLine);
       }

       void LeftOuterJoin()
       {
           // Create the query.
           var leftOuterQuery =
              from category in categories
              join prod in products on category.ID equals prod.CategoryID into prodGroup
              select prodGroup.DefaultIfEmpty(new Product() { Name = "Nothing!", CategoryID = category.ID });

           // Store the count of total items (for demonstration only).
           int totalItems = 0;

           Console.WriteLine("Left Outer Join:");

           // A nested foreach statement  is required to access group items
           foreach (var prodGrouping in leftOuterQuery)
           {
               Console.WriteLine("Group:");
               foreach (var item in prodGrouping)
               {
                   totalItems++;
                   Console.WriteLine("  {0,-10}{1}", item.Name, item.CategoryID);
               }
           }
           Console.WriteLine("LeftOuterJoin: {0} items in {1} groups", totalItems, leftOuterQuery.Count());
           Console.WriteLine(System.Environment.NewLine);
       }

       void LeftOuterJoin2()
       {
           // Create the query.
           var leftOuterQuery2 =
              from category in categories
              join prod in products on category.ID equals prod.CategoryID into prodGroup
              from item in prodGroup.DefaultIfEmpty()
              select new { Name = item == null ? "Nothing!" : item.Name, CategoryID = category.ID };

           Console.WriteLine("LeftOuterJoin2: {0} items in 1 group", leftOuterQuery2.Count());
           // Store the count of total items
           int totalItems = 0;

           Console.WriteLine("Left Outer Join 2:");

           // Groups have been flattened.
           foreach (var item in leftOuterQuery2)
           {
               totalItems++;
               Console.WriteLine("{0,-10}{1}", item.Name, item.CategoryID);
           }
           Console.WriteLine("LeftOuterJoin2: {0} items in 1 group", totalItems);
       }
   }
   /*Output:

   InnerJoin:
   Cola      1
   Tea       1
   Mustard   2
   Pickles   2
   Carrots   3
   Bok Choy  3
   Peaches   5
   Melons    5
   InnerJoin: 8 items in 1 group.


   Unshaped GroupJoin:
   Group:
       Cola      1
       Tea       1
   Group:
       Mustard   2
       Pickles   2
   Group:
       Carrots   3
       Bok Choy  3
   Group:
   Group:
       Peaches   5
       Melons    5
   Unshaped GroupJoin: 8 items in 5 unnamed groups


   GroupInnerJoin:
   Beverages
       Cola       1
       Tea        1
   Condiments
       Mustard    2
       Pickles    2
   Vegetables
       Bok Choy   3
       Carrots    3
   Grains
   Fruit
       Melons     5
       Peaches    5
   GroupInnerJoin: 8 items in 5 named groups


   GroupJoin3:
       Cola:1
       Tea:1
       Mustard:2
       Pickles:2
       Carrots:3
       Bok Choy:3
       Peaches:5
       Melons:5
   GroupJoin3: 8 items in 1 group


   Left Outer Join:
   Group:
       Cola      1
       Tea       1
   Group:
       Mustard   2
       Pickles   2
   Group:
       Carrots   3
       Bok Choy  3
   Group:
       Nothing!  4
   Group:
       Peaches   5
       Melons    5
   LeftOuterJoin: 9 items in 5 groups


   LeftOuterJoin2: 9 items in 1 group
   Left Outer Join 2:
   Cola      1
   Tea       1
   Mustard   2
   Pickles   2
   Carrots   3
   Bok Choy  3
   Nothing!  4
   Peaches   5
   Melons    5
   LeftOuterJoin2: 9 items in 1 group
   Press any key to exit.
   */

备注Remarks

后面未跟 intojoin 子句转换为 Join 方法调用。A join clause that is not followed by into is translated into a Join method call. 后面跟 intojoin 子句转换为 GroupJoin 方法调用。A join clause that is followed by into is translated to a GroupJoin method call.

请参阅See also