Linq 集合操作

Linq 集合操作

演示代码

两个对象一个是Person,一个Address, AddressId是外键,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
public class Person
 
{
 
public string ID { get; set; }
 
public string Name { get; set; }
 
public int Age { get; set; }
 
public double Salary { get; set; }
 
public DateTime Born { get; set; }
 
public int IdAddress { get; set; }
 
}
 
  
 
public class Address
 
{
 
public int IdAddress { get; set; }
 
public string Street { get; set; }
 
public int Num { get; set; }
 
public string City { get; set; }
 
}

  

 

测试数据如下

Person类

Address类

下面我会用7个方式实现7中集合操作

  1. INNER JOIN 内链接
  2. LEFT JOIN 左连接
  3. RIGHT JOIN 右链接
  4. FULL OUTER JOIN 所有
  5. LEFT JOIN EXCLUDING INNER JOIN 左空
  6. RIGHT JOIN EXCLUDING INNER JOIN 右空
  7. FULL OUTER JOIN EXCLUDING INNER JOIN ??

    学校数学没学好不知道专业术语!哈哈

    INNER JOIN  

    最常用的方法,两表关联查询

    标准Linq语法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    var result = from p in Person.BuiltPersons()
     
    join a in Address.BuiltAddresses()
     
    on p.IdAddress equals a.IdAddress
     
    select new
     
         {
     
    Name = a.MyPerson.Name,
     
    Age = a.MyPerson.Age,
     
    PersonIdAddress = a.MyPerson.IdAddress,
     
    AddressIdAddress = a.MyAddress.IdAddress,
     
    Street = a.MyAddress.Street
     
         };

      

    Lambda Expression: 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    var resultJoint = Person.BuiltPersons().Join( /// Source Collection
     
    Address.BuiltAddresses(), /// Inner Collection
     
    p => p.IdAddress, /// PK
     
    a => a.IdAddress, /// FK
     
    (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection
     
    .Select(a => new
     
    {
     
    Name = a.MyPerson.Name,
     
    Age = a.MyPerson.Age,
     
    PersonIdAddress = a.MyPerson.IdAddress,
     
    AddressIdAddress = a.MyAddress.IdAddress,
     
    Street = a.MyAddress.Street
     
    });

      

     

    Lambda表达式主要有5部分

  8. Is the main Collection. 
  9. Is the inner Collection.
  10. Is the PK.
  11. Is the FK.
  12. Is the type for the result collection. 

    查询结果如下

    LEFT JOIN

    新增一个LeftJoin的扩展方法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    public static IEnumerable<TResult>
     
        LeftJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
     
    IEnumerable<TInner> inner,
     
    Func<TSource, TKey> pk,
     
    Func<TInner, TKey> fk,
     
    Func<TSource, TInner, TResult> result)
     
    {
     
    IEnumerable<TResult> _result = Enumerable.Empty<TResult>();
     
      
     
    _result = from s in source
     
    join i in inner
     
    on pk(s) equals fk(i) into joinData
     
    from left in joinData.DefaultIfEmpty()
     
    select result(s, left);
     
      
     
    return _result;
     
    }

      

     Lambda Expression: 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    var resultJoint = Person.BuiltPersons().LeftJoin( /// Source Collection
     
    Address.BuiltAddresses(), /// Inner Collection
     
    p => p.IdAddress, /// PK
     
    a => a.IdAddress, /// FK
     
    (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection
     
    .Select(a => new
     
    {
     
    Name = a.MyPerson.Name,
     
    Age = a.MyPerson.Age,
     
    PersonIdAddress = a.MyPerson.IdAddress,
     
    AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1),
     
                Street = (a.MyAddress != null ? a.MyAddress.Street : "Null-Value")
     
    });

      

    注意:如果address为空Null需要做一个替换,否则会报错

    查询结果如下

    RIGHT JOIN 

    Extension Method: 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    public static IEnumerable<TResult>
     
        RightJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
     
    IEnumerable<TInner> inner,
     
    Func<TSource, TKey> pk,
     
    Func<TInner, TKey> fk,
     
    Func<TSource, TInner, TResult> result)
     
    {
     
    IEnumerable<TResult> _result = Enumerable.Empty<TResult>();
     
      
     
    _result = from i in inner
     
    join s in source
     
    on fk(i) equals pk(s) into joinData
     
    from right in joinData.DefaultIfEmpty()
     
    select result(right, i);
     
      
     
    return _result;
     
    }

      

       

     Lambda Expression: 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    var resultJoint = Person.BuiltPersons().RightJoin( /// Source Collection
     
    Address.BuiltAddresses(), /// Inner Collection
     
    p => p.IdAddress, /// PK
     
    a => a.IdAddress, /// FK
     
    (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection
     
    .Select(a => new
     
    {
     
    Name = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"),
     
    Age = (a.MyPerson != null ? a.MyPerson.Age : -1),
     
    PersonIdAddress = (a.MyPerson != null ? a.MyPerson.IdAddress : -1),
     
    AddressIdAddress = a.MyAddress.IdAddress,
     
    Street = a.MyAddress.Street
     
    });

      

     

     

    查询结果如下

    FULL OUTER JOIN 

    Extension Method: 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    public static IEnumerable<TResult>
     
        FullOuterJoinJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
     
    IEnumerable<TInner> inner,
     
    Func<TSource, TKey> pk,
     
    Func<TInner, TKey> fk,
     
    Func<TSource, TInner, TResult> result)
     
    {
     
      
     
    var left = source.LeftJoin(inner, pk, fk, result).ToList();
     
    var right = source.RightJoin(inner, pk, fk, result).ToList();
     
      
     
    return left.Union(right);
     
    }

      

     Lambda Expression: 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    var resultJoint = Person.BuiltPersons().FullOuterJoinJoin( /// Source Collection
     
    Address.BuiltAddresses(), /// Inner Collection
     
    p => p.IdAddress, /// PK
     
    a => a.IdAddress, /// FK
     
    (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection
     
    .Select(a => new
     
    {
     
    Name = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"),
     
    Age = (a.MyPerson != null ? a.MyPerson.Age : -1),
     
    PersonIdAddress = (a.MyPerson != null ? a.MyPerson.IdAddress : -1),
     
    AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1),
     
    Street = (a.MyAddress != null ? a.MyAddress.Street : "Null-Value")
     
    });

      

     

    注意:每个对象都需要验证Null

    查询结果如下

    LEFT EXCLUDING JOIN 

    Extension Method: 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    public static IEnumerable<TResult>
     
        LeftExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
     
    IEnumerable<TInner> inner,
     
    Func<TSource, TKey> pk,
     
    Func<TInner, TKey> fk,
     
    Func<TSource, TInner, TResult> result)
     
    {
     
    IEnumerable<TResult> _result = Enumerable.Empty<TResult>();
     
      
     
    _result = from s in source
     
    join i in inner
     
    on pk(s) equals fk(i) into joinData
     
    from left in joinData.DefaultIfEmpty()
     
    where left == null
     
    select result(s, left);
     
      
     
    return _result;
     
    }

      

     Lambda Expression:  

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    var resultJoint = Person.BuiltPersons().LeftExcludingJoin( /// Source Collection
     
    Address.BuiltAddresses(), /// Inner Collection
     
    p => p.IdAddress, /// PK
     
    a => a.IdAddress, /// FK
     
    (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection
     
    .Select(a => new
     
    {
     
    Name = a.MyPerson.Name,
     
    Age = a.MyPerson.Age,
     
    PersonIdAddress = a.MyPerson.IdAddress,
     
    AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1),
     
    Street = (a.MyAddress != null ? a.MyAddress.Street : "Null-Value")
     
    });

      

    查询结果如下

    RIGHT EXCLUDING JOIN

    Extension Method:  

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    public static IEnumerable<TResult>
     
    RightExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
     
    IEnumerable<TInner> inner,
     
    Func<TSource, TKey> pk,
     
    Func<TInner, TKey> fk,
     
    Func<TSource, TInner, TResult> result)
     
    {
     
    IEnumerable<TResult> _result = Enumerable.Empty<TResult>();
     
      
     
    _result = from i in inner
     
    join s in source
     
    on fk(i) equals pk(s) into joinData
     
    from right in joinData.DefaultIfEmpty()
     
    where right == null
     
    select result(right, i);
     
      
     
    return _result;
     
    }

      

     Lambda Expression:   

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    var resultJoint = Person.BuiltPersons().RightExcludingJoin( /// Source Collection
     
    Address.BuiltAddresses(), /// Inner Collection
     
    p => p.IdAddress, /// PK
     
    a => a.IdAddress, /// FK
     
    (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection
     
    .Select(a => new
     
    {
     
    Name = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"),
     
    Age = (a.MyPerson != null ? a.MyPerson.Age : -1),
     
    PersonIdAddress = (a.MyPerson != null ? a.MyPerson.IdAddress : -1),
     
    AddressIdAddress = a.MyAddress.IdAddress,
     
    Street = a.MyAddress.Street
     
    });

      

     

    查询结果

    FULL OUTER EXCLUDING JOIN  

    Extension Method:  

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    public static IEnumerable<TResult>
     
    FulltExcludingJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source,
     
    IEnumerable<TInner> inner,
     
    Func<TSource, TKey> pk,
     
    Func<TInner, TKey> fk,
     
    Func<TSource, TInner, TResult> result)
     
    {
     
    var left = source.LeftExcludingJoin(inner, pk, fk, result).ToList();
     
    var right = source.RightExcludingJoin(inner, pk, fk, result).ToList();
     
      
     
    return left.Union(right);
     
    }

      

     Lambda Expression:   

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    var resultJoint = Person.BuiltPersons().FulltExcludingJoin( /// Source Collection
     
    Address.BuiltAddresses(), /// Inner Collection
     
    p => p.IdAddress, /// PK
     
    a => a.IdAddress, /// FK
     
    (p, a) => new { MyPerson = p, MyAddress = a }) /// Result Collection
     
    .Select(a => new
     
    {
     
    Name = (a.MyPerson != null ? a.MyPerson.Name : "Null-Value"),
     
    Age = (a.MyPerson != null ? a.MyPerson.Age : -1),
     
    PersonIdAddress = (a.MyPerson != null ? a.MyPerson.IdAddress : -1),
     
    AddressIdAddress = (a.MyAddress != null ? a.MyAddress.IdAddress : -1),
     
    Street = (a.MyAddress != null ? a.MyAddress.Street : "Null-Value")
     
    });

      

     

    查询结果

posted @   阿新  阅读(517)  评论(0编辑  收藏  举报
编辑推荐:
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
阅读排行:
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
历史上的今天:
2015-02-13 Asp.net mvc 5 CRUD代码自动生成工具- vs.net 2013 Saffolding功能扩展
点击右上角即可分享
微信分享提示