Chemmy's Blog

LINQ查询表达式（4） - LINQ Join联接 - 水手哥 - 博客园

Excerpt

内部联接 按照关系数据库的说法，“内部联接”产生一个结果集，对于该结果集内第一个集合中的每个元素，只要在第二个集合中存在一个匹配元素，该元素就会出现一次。 如果第一个集合中的某个元素没有匹配元素，则它不会出现在结果集内。 Join 方法（通过 C# 中的 join 子句调用）可实现内联。 内部连接的

---

内部联接

　　按照关系数据库的说法，“内部联接”产生一个结果集，对于该结果集内第一个集合中的每个元素，只要在第二个集合中存在一个匹配元素，该元素就会出现一次。 如果第一个集合中的某个元素没有匹配元素，则它不会出现在结果集内。 Join 方法（通过 C# 中的 join 子句调用）可实现内联。

　　内部连接的4种变体：

• 简单联接，它基于一个简单的键将来自两个数据源的元素相互关联。
• 复合联接，它基于一个复合键将来自两个数据源的元素相互关联。 使用复合键（即由多个值组成的键）可以基于多个属性将元素相互关联。
• 多联接，在其中连续的联接操作被相互拼接在一起。
• 分组联接

　　下面分别描述：

1. 内部联接：简单键联接

   

       　　　　 class<span> Person
   {
       public string FirstName { get; set<span>; }
       public string LastName { get; set<span>; }
   }
       
   class<span> Pet
   {
       public string Name { get; set<span>; }
       public Person Owner { get; set<span>; }
   }
       
   /// &lt;summary&gt;
   /// Simple inner join.
   /// &lt;/summary&gt;
   public static void<span> InnerJoinExample()
   {          
       // 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<span> people
                   join pet in<span> pets on person equals pet.Owner
                   select new { OwnerName = person.FirstName, PetName = pet.Name };<br>　　　　 }</span></span></span></span></span></span></span></span></span>

   

2. 内部联接：复合联接
   　　与仅仅基于一个属性将元素相互关联不同，使用复合键可基于多个属性来比较元素。 为此，需要为每个集合指定键选择器函数，以便返回一个由要比较的属性组成的匿名类型。 如果给属性加上了标签，则这些属性必须在每个键的匿名类型中都有相同的标签， 而且还必须以相同顺序出现。

   

   <span>class</span><span> Employee
           {
               </span><span>public</span> <span>string</span> FirstName { <span>get</span>; <span>set</span><span>; }
               </span><span>public</span> <span>string</span> LastName { <span>get</span>; <span>set</span><span>; }
               </span><span>public</span> <span>int</span> EmployeeID { <span>get</span>; <span>set</span><span>; }
           }
   
           </span><span>class</span><span> Student
           {
               </span><span>public</span> <span>string</span> FirstName { <span>get</span>; <span>set</span><span>; }
               </span><span>public</span> <span>string</span> LastName { <span>get</span>; <span>set</span><span>; }
               </span><span>public</span> <span>int</span> StudentID { <span>get</span>; <span>set</span><span>; }
           }
   
    IEnumerable</span>&lt;<span>string</span>&gt; query = <span>from</span> employee <span>in</span><span> employees
                                           join student </span><span>in</span><span> students
                                           on </span><span>new</span><span> { employee.FirstName, employee.LastName }
                                           equals </span><span>new</span><span> { student.FirstName, student.LastName }
                                           </span><span>select</span> employee.FirstName + <span>"</span> <span>"</span> + employee.LastName;

   

3. 内部连接：多联接
       可以将任意数量的联接操作拼接在一起以执行多联接。 C# 中的每一个 join 子句都可将指定的数据源与前一个联接的结果相互关联。

   

         　　   <span>class</span><span> Person
   {
       </span><span>public</span> <span>string</span> FirstName { <span>get</span>; <span>set</span><span>; }
       </span><span>public</span> <span>string</span> LastName { <span>get</span>; <span>set</span><span>; }
   }
   </span><span>class</span><span> Pet
   {
       </span><span>public</span> <span>string</span> Name { <span>get</span>; <span>set</span><span>; }
       </span><span>public</span> Person Owner { <span>get</span>; <span>set</span><span>; }
   }
   </span><span>class</span><span> Cat : Pet
   { }
   </span><span>class</span><span> Dog : Pet
   { }
       
       </span><span>//</span><span> The first join matches Person and Cat.Owner from the list of people and
       </span><span>//</span><span> cats, based on a common Person. The second join matches dogs whose names start
       </span><span>//</span><span> with the same letter as the cats that have the same owner.</span>
       <span>var</span> query = <span>from</span> person <span>in</span><span> people
                   join cat </span><span>in</span><span> cats on person equals cat.Owner
                   join dog </span><span>in</span><span> dogs on 
                   </span><span>new</span> { Owner = person, Letter = cat.Name.Substring(<span>0</span>, <span>1</span><span>) }
                   equals </span><span>new</span> { dog.Owner, Letter = dog.Name.Substring(<span>0</span>, <span>1</span><span>) }
                   </span><span>select</span> <span>new</span> { CatName = cat.Name, DogName = dog.Name };

   

4. 内部连接：分组联接实现内部联接
        在 query1 中，Person 对象列表基于与 Pet.Owner 属性匹配的 Person 分组联接到 Pet 对象列表。 分组联接创建了一个中间组集合，该集合中的每个组都由一个 Person 对象和匹配的 Pet 对象序列组成。

   

   <span>        class</span><span> Person
           {
               </span><span>public</span> <span>string</span> FirstName { <span>get</span>; <span>set</span><span>; }
               </span><span>public</span> <span>string</span> LastName { <span>get</span>; <span>set</span><span>; }
           }
   
           </span><span>class</span><span> Pet
           {
               </span><span>public</span> <span>string</span> Name { <span>get</span>; <span>set</span><span>; }
               </span><span>public</span> Person Owner { <span>get</span>; <span>set</span><span>; }
           }
   
   </span><span>        var</span> query1 = <span>from</span> person <span>in</span><span> people
                            join pet </span><span>in</span><span> pets on person equals pet.Owner into gj
                            </span><span>from</span> subpet <span>in</span><span> gj
                            </span><span>select</span> <span>new</span> { OwnerName = person.FirstName, PetName = subpet.Name };

   

　　　　分组联接示例：执行分组联接以创建 XML 的示例

　　　　分组联接非常适合于使用 LINQ to XML 来创建 XML。 本例结果选择器函数创建表示已联接对象的 XML 元素，而不是创建匿名类型。

<span>　　　　　class</span><span> Person
        {
            </span><span>public</span> <span>string</span> FirstName { <span>get</span>; <span>set</span><span>; }
            </span><span>public</span> <span>string</span> LastName { <span>get</span>; <span>set</span><span>; }
        }
        </span><span>class</span><span> Pet
        {
            </span><span>public</span> <span>string</span> Name { <span>get</span>; <span>set</span><span>; }
            </span><span>public</span> Person Owner { <span>get</span>; <span>set</span><span>; }
        }

    　　XElement ownersAndPets </span>= <span>new</span> XElement(<span>"</span><span>PetOwners</span><span>"</span><span>,
                </span><span>from</span> person <span>in</span><span> people
                join pet </span><span>in</span><span> pets on person equals pet.Owner into gj
                </span><span>select</span> <span>new</span> XElement(<span>"</span><span>Person</span><span>"</span><span>,
                    </span><span>new</span> XAttribute(<span>"</span><span>FirstName</span><span>"</span><span>, person.FirstName),
                    </span><span>new</span> XAttribute(<span>"</span><span>LastName</span><span>"</span><span>, person.LastName),
                    </span><span>from</span> subpet <span>in</span><span> gj
                    </span><span>select</span> <span>new</span> XElement(<span>"</span><span>Pet</span><span>"</span>, subpet.Name)));&nbsp;&nbsp;

外部连接

• 外部联接（左外部联接）

     左外部联接是这样一个联接：在其中返回第一个集合的每个元素，而无论该元素在第二个集合中是否具有相关元素。 可以使用 LINQ 执行左通过对分组联接的结果调用方法 DefaultIfEmpty 外部联接连接。

<span>class</span><span> Person
{
    </span><span>public</span> <span>string</span> FirstName { <span>get</span>; <span>set</span><span>; }
    </span><span>public</span> <span>string</span> LastName { <span>get</span>; <span>set</span><span>; }
}

</span><span>class</span><span> Pet
{
    </span><span>public</span> <span>string</span> Name { <span>get</span>; <span>set</span><span>; }
    </span><span>public</span> Person Owner { <span>get</span>; <span>set</span><span>; }
}  

  　　　　   </span><span>//</span><span> Create two lists.</span>
    List&lt;Person&gt; people = <span>new</span> List&lt;Person&gt;<span> { magnus, terry, charlotte, arlene };
    List</span>&lt;Pet&gt; pets = <span>new</span> List&lt;Pet&gt;<span> { barley, boots, whiskers, bluemoon, daisy };

    </span><span>var</span> query = <span>from</span> person <span>in</span><span> people
                join pet </span><span>in</span><span> pets on person equals pet.Owner into gj
                </span><span>from</span> subpet <span>in</span><span> gj.DefaultIfEmpty()
                </span><span>select</span> <span>new</span> { person.FirstName, PetName = (subpet == <span>null</span> ? String.Empty : subpet.Name) };

•  交差联接

 　　　　　　<span>var</span> crossJoinQuery =
<span>from</span> c <span>in</span><span> categories
</span><span>from</span> p <span>in</span><span> products
</span><span>select</span> <span>new</span> { c.ID, p.Name };

• 自定义的联接操作

<span>　　　　　　　　　　　var</span> nonEquijoinQuery =
                        <span>from</span> p <span>in</span><span> products
                        let catIds </span>= <span>from</span> c <span>in</span><span> categories
                                     </span><span>select</span><span> c.ID
                        </span><span>where</span> catIds.Contains(p.CategoryID) == <span>true</span>
                        <span>select</span> <span>new</span> { Product = p.Name, CategoryID = p.CategoryID };

参考

　　[1] MSDN,执行内部连接

　　[2] MSDN,执行分组连接