C# List.Join()方法 表链接查询

原文链接

最近在研究C#泛型集合的时候发现了List.Join方法，这个方法与C#中的string.Join方法还有js中的join方法不一样，不是根据分隔符链接字符串，而是根据两个对象关联起来生成新的数据。

 

List.Join方法更像SQL 中的JOIN连接，该方法是根据两个泛型集合之间的关系，将这两个集合合并后获取新的集合。而SQL的JOIN 则是根据两个或多个表中的列之间的关系，从这些表中查询数据。（PS:具体可以参考微软官方MSDN的说明)

这是整理后的官方范例代码（代码在控制台应用程序中运行）：

    /*宠物主人*/
    class Person
    {
        public string Name { get; set; }
    }
    /*宠物*/
    class Pet
    {
        public string Name { get; set; }
        public Person Owner { get; set; }
    }
    static void Main(string[] args)
    {
        /*宠物主人*/
        Person magnus = new Person { Name = "Hedlund, Magnus" };
        Person terry = new Person { Name = "Adams, Terry" };
        Person charlotte = new Person { Name = "Weiss, Charlotte" };

    <span class="hljs-comment"><span class="hljs-comment">/*宠物*/</span>
    Pet barley = <span class="hljs-keyword"><span class="hljs-keyword">new</span> Pet { Name = <span class="hljs-string"><span class="hljs-string">"Barley"</span>, Owner = terry };
    Pet boots = <span class="hljs-keyword"><span class="hljs-keyword">new</span> Pet { Name = <span class="hljs-string"><span class="hljs-string">"Boots"</span>, Owner = terry };
    Pet whiskers = <span class="hljs-keyword"><span class="hljs-keyword">new</span> Pet { Name = <span class="hljs-string"><span class="hljs-string">"Whiskers"</span>, Owner = charlotte };
    Pet daisy = <span class="hljs-keyword"><span class="hljs-keyword">new</span> Pet { Name = <span class="hljs-string"><span class="hljs-string">"Daisy"</span>, Owner = magnus };

    <span class="hljs-comment"><span class="hljs-comment">/*宠物主人列表集合*/</span>
    List&lt;Person&gt; people = <span class="hljs-keyword"><span class="hljs-keyword">new</span> List&lt;Person&gt; { magnus, terry, charlotte };
    <span class="hljs-comment"><span class="hljs-comment">/*宠物列表集合*/</span>
    List&lt;Pet&gt; pets = <span class="hljs-keyword"><span class="hljs-keyword">new</span> List&lt;Pet&gt; { barley, boots, whiskers, daisy };

    <span class="hljs-comment"><span class="hljs-comment">/*          
     * Create a list of Person-Pet pairs where 
     * each element is an anonymous type that contains a
     * Pet's name and the name of the Person that owns the Pet.
     * 创建一个包含 "主人-宠物" 这样对应对象的列表
     * ，其中每个对象元素都是包含宠物名字和宠物主人名字的匿名类型
    */</span>            
    <span class="hljs-keyword"><span class="hljs-keyword">var</span> query = people.Join(pets, person =&gt; person, pet =&gt; pet.Owner
    , (person, pet) =&gt; <span class="hljs-keyword"><span class="hljs-keyword">new</span> { OwnerName = person.Name, Pet = pet.Name });

    <span class="hljs-comment"><span class="hljs-comment">/*循环输出最终结果 格式：宠物主人名-宠物名*/</span>
    <span class="hljs-keyword"><span class="hljs-keyword">foreach</span> (<span class="hljs-keyword"><span class="hljs-keyword">var</span> obj <span class="hljs-keyword"><span class="hljs-keyword">in</span> query)
    {
        Console.WriteLine(<span class="hljs-string"><span class="hljs-string">"{0} - {1}"</span>, obj.OwnerName, obj.Pet);
    }

    Console.ReadKey();
}</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></pre>




最终控制台输出的结果如下：







下面使用SQL语句JOIN连接查询，从而模拟出List.Join方法的同等效果，其中有Person和Pet这两张表，根据Pet宠物表的PID关联Person宠物主人表的ID。




Person表数据如下：





id	Name
1	Hedlund, Magnus
2	Adams, Terry
3	Weiss, Charlotte





Pet表数据如下：





id	Name	pid
1	Daisy	1
2	Barley	2
3	Boots	2
4	Whiskers	3





查询语句如下：



SELECT P.Name+' - '+PT.Name AS '宠物主人 - 宠物名称'
FROM Person AS P JOIN Pet AS PT 
ON P.Id=PT.PID



查询结果如下：





宠物主人 - 宠物名称
Hedlund, Magnus - Daisy
Adams, Terry - Barley
Adams, Terry - Boots
Weiss, Charlotte - Whiskers





最终结果和上面使用List.Join方法获取的一模一样吧。








作者：十有三




出处：https://shiyousan.com/post/635434860251469582




版权声明：本文采用知识共享许可协议：署名-相同方式共享 4.0 国际(CC BY-SA 4.0)。欢迎转载本文，转载请声明出处或保留此段声明。




 




    <span class="hljs-comment"><span class="hljs-comment">/*宠物*/</span>
    Pet barley = <span class="hljs-keyword"><span class="hljs-keyword">new</span> Pet { Name = <span class="hljs-string"><span class="hljs-string">"Barley"</span>, Owner = terry };
    Pet boots = <span class="hljs-keyword"><span class="hljs-keyword">new</span> Pet { Name = <span class="hljs-string"><span class="hljs-string">"Boots"</span>, Owner = terry };
    Pet whiskers = <span class="hljs-keyword"><span class="hljs-keyword">new</span> Pet { Name = <span class="hljs-string"><span class="hljs-string">"Whiskers"</span>, Owner = charlotte };
    Pet daisy = <span class="hljs-keyword"><span class="hljs-keyword">new</span> Pet { Name = <span class="hljs-string"><span class="hljs-string">"Daisy"</span>, Owner = magnus };

    <span class="hljs-comment"><span class="hljs-comment">/*宠物主人列表集合*/</span>
    List&lt;Person&gt; people = <span class="hljs-keyword"><span class="hljs-keyword">new</span> List&lt;Person&gt; { magnus, terry, charlotte };
    <span class="hljs-comment"><span class="hljs-comment">/*宠物列表集合*/</span>
    List&lt;Pet&gt; pets = <span class="hljs-keyword"><span class="hljs-keyword">new</span> List&lt;Pet&gt; { barley, boots, whiskers, daisy };

    <span class="hljs-comment"><span class="hljs-comment">/*          
     * Create a list of Person-Pet pairs where 
     * each element is an anonymous type that contains a
     * Pet's name and the name of the Person that owns the Pet.
     * 创建一个包含 "主人-宠物" 这样对应对象的列表
     * ，其中每个对象元素都是包含宠物名字和宠物主人名字的匿名类型
    */</span>            
    <span class="hljs-keyword"><span class="hljs-keyword">var</span> query = people.Join(pets, person =&gt; person, pet =&gt; pet.Owner
    , (person, pet) =&gt; <span class="hljs-keyword"><span class="hljs-keyword">new</span> { OwnerName = person.Name, Pet = pet.Name });

    <span class="hljs-comment"><span class="hljs-comment">/*循环输出最终结果 格式：宠物主人名-宠物名*/</span>
    <span class="hljs-keyword"><span class="hljs-keyword">foreach</span> (<span class="hljs-keyword"><span class="hljs-keyword">var</span> obj <span class="hljs-keyword"><span class="hljs-keyword">in</span> query)
    {
        Console.WriteLine(<span class="hljs-string"><span class="hljs-string">"{0} - {1}"</span>, obj.OwnerName, obj.Pet);
    }

    Console.ReadKey();
}</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></pre>
SELECT P.Name+' - '+PT.Name AS '宠物主人 - 宠物名称'
FROM Person AS P JOIN Pet AS PT 
ON P.Id=PT.PID