join 子句(C# 参考)
  • 2015/07/20
  • 内部联接
  • 分组联接
  • 左外部联接
  • 内部联接
    以下示例演示了一个简单的内部同等联接。 此查询生成一个“产品名称/类别”对平面序列。 同一类别字符串将出现在多个元素中。 如果 categories 中的某个元素不具有匹配的 products,则该类别不会出现在结果中。
    C#复制
    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
    有关详细信息,请参阅执行内联
    分组联接
    含有 into 表达式的 join 子句称为分组联接。
    C#复制
    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 };
    分组联接会生成分层的结果序列,该序列将左侧源序列中的元素与右侧源序列中的一个或多个匹配元素相关联。 分组联接没有等效的关系术语;它本质上是一个对象数组序列。
    如果在右侧源序列中找不到与左侧源中的元素相匹配的元素,则 join 子句会为该项生成一个空数组。 因此,分组联接基本上仍然是一种内部同等联接,区别在于分组联接将结果序列组织为多个组。
    如果只选择分组联接的结果,则可访问各项,但无法识别结果所匹配的项。 因此,通常更为有用的做法是:选择分组联接的结果并将其放入一个也包含该项名的新类型中,如上例所示。
    当然,还可以将分组联接的结果用作其他子查询的生成器:
    C#复制
    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;
    有关详细信息,请参阅执行分组联接
    左外部联接
    在左外部联接中,将返回左侧源序列中的所有元素,即使右侧序列中没有其匹配元素也是如此。 若要在 LINQ 中执行左外部联接,请结合使用 DefaultIfEmpty 方法与分组联接,指定要在某个左侧元素不具有匹配元素时生成的默认右侧元素。 可以使用 null 作为任何引用类型的默认值,也可以指定用户定义的默认类型。 以下示例演示了用户定义的默认类型:
    C#复制
    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 };
    有关详细信息,请参阅执行左外部联接
    等于运算符
    join 子句执行同等联接。 换言之,只能基于 2 个项之间的相等关系进行匹配。 不支持其他类型的比较,例如“大于”或“不等于”。 为了表明所有联接都是同等联接,join 子句使用 equals 关键字而不是 == 运算符。 equals 关键字只能在 join 子句中使用,并且其与 == 运算符之间存在一个重要差别。 对于 equals,左键使用外部源序列,而右键使用内部源序列。 外部源仅在 equals 的左侧位于范围内,而内部源序列仅在其右侧位于范围内。
    非同等联接
    通过使用多个 from 子句将新序列单独引入查询,可以执行非同等联接、交叉联接和其他自定义联接操作。 有关详细信息,请参阅执行自定义联接操作
    对象集合联接与关系表
    在LINQ 查询表达式中,联接操作是在对象集合上执行的。 不能使用与 2 个关系表完全相同的方式“联接”对象集合。 在 LINQ 中,仅当 2 个源序列没有通过任何关系相互联系时,才需要使用显式 join 子句。 使用 LINQ to SQL 时,外键表在对象模型中表示为主表的属性。 例如,在 Northwind 数据库中,Customer 表与 Orders 表之间具有外键关系。 将这两个表映射到对象模型时,Customer 类具有一个 Orders 属性,其中包含与该 Customer 相关联的 Orders 集合。 实际上,已经为你执行了联接。
    若要详细了解如何在 LINQ to SQL 的上下文中跨相关表执行查询,请参阅操作说明:映射数据库关系
    组合键
    可通过使用组合键测试多个值是否相等。 有关详细信息,请参阅使用组合键进行联接。 还可以在 group 子句中使用组合键。
    示例
    以下示例比较了使用相同的匹配键对相同数据源执行内部联接、分组联接和左外部联接的结果。 这些示例中添加了一些额外的代码,以便在控制台显示中阐明结果。
    C#复制
    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, groupJoinQuery3.Count()); 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:", prodGrouping.Count()); 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. */
    备注
    后面未跟 into 的 join 子句转换为 Join 方法调用。 后面跟 into 的 join 子句转换为 GroupJoin 方法调用。
    请参阅