Fork me on GitHub

Linq知识集四（翻译）

字数统计: 1.9k字   |   阅读时长≈ 9分

原文起始连接：https://dotnettutorials.net/course/linq/

说明：翻译接上篇，上一篇介绍了LINQ中的排序、聚合、分组、量化等操作。本篇继续介绍LINQ中的其他操作。包括Join、GroupJoin、Cross Join、Left Join。以及针对数据源元素的操作。

LINQ连接操作

和SQL连接操作一样，用于连接两个数据源。LINQ中提供下面两个方法：

  • Join 连接两个数据源，返回一个数据
  • GroupJoin 连接两个数据源，返回一组数据

连接操作

Inner Join方法

首先来看Join方法的签名。从签名中可以看出，理解Linq中的Join，需要理解一下5个内容

  • Outer data source 外部数据源
  • Inner data source 内部数据源
  • Outer Key selector 外部数据源的键
  • Inner Key selector 内部数据源的键
  • Result selector 将数据映射到结果集
1
2
3
public static IEnumerable<TResult> Join<TOuter, Tinner, TKey, TResult>(this IEnumerable<TOuter> outer, IEnumerable<Tinner> inner, Func<TOuter, TKey> outerKeySelector, Func<Tinner, TKey> innerKeySelector, Func<TOuter, Tinner, TResult> resultSelector);

public static IEnumerable<TResult> Join<TOuter, Tinner, TKey, TResult>(this IEnumerable<TOuter> outer, IEnumerable<Tinner> inner, Func<TOuter, TKey> outerKeySelector, Func<Tinner, TKey> innerKeySelector, Func<TOuter, Tinner, TResult> resultSelector);

实例数据

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
public class Employee
 {
     public int ID { get; set; }
     public string Name { get; set; }
     public int AddressId { get; set; }
     public static List<Employee> GetAllEmployees()
     {
         return new List<Employee>()
         {
             new Employee { ID = 1, Name = "Preety", AddressId = 1, DepartmentId = 10    },
             new Employee { ID = 2, Name = "Priyanka", AddressId = 2, DepartmentId =20   },
             new Employee { ID = 3, Name = "Anurag", AddressId = 3, DepartmentId = 30    },
             new Employee { ID = 4, Name = "Pranaya", AddressId = 4, DepartmentId = 0    },
             new Employee { ID = 5, Name = "Hina", AddressId = 5, DepartmentId = 0       },
             new Employee { ID = 6, Name = "Sambit", AddressId = 6, DepartmentId = 0     },
             new Employee { ID = 7, Name = "Happy", AddressId = 7, DepartmentId = 0      },
             new Employee { ID = 8, Name = "Tarun", AddressId = 8, DepartmentId = 0      },
             new Employee { ID = 9, Name = "Santosh", AddressId = 9, DepartmentId = 10   },
             new Employee { ID = 10, Name = "Raja", AddressId = 10, DepartmentId = 20    },
             new Employee { ID = 11, Name = "Ramesh", AddressId = 11, DepartmentId = 30  }
         };
     }
 }
 public class Address
 {
     public int ID { get; set; }
     public string AddressLine { get; set; }
     public static List<Address> GetAllAddresses()
     {
         return new List<Address>()
         {
             new Address { ID = 1, AddressLine = "AddressLine1"},
             new Address { ID = 2, AddressLine = "AddressLine2"},
             new Address { ID = 3, AddressLine = "AddressLine3"},
             new Address { ID = 4, AddressLine = "AddressLine4"},
             new Address { ID = 5, AddressLine = "AddressLine5"},
             new Address { ID = 9, AddressLine = "AddressLine9"},
             new Address { ID = 10, AddressLine = "AddressLine10"},
             new Address { ID = 11, AddressLine = "AddressLine11"},
         };
     }
 }
 public class Department
 {
     public int ID { get; set; }
     public string Name { get; set; }
     public static List<Department> GetAllDepartments()
     {
         return new List<Department>()
             {
                 new Department { ID = 10, Name = "IT"       },
                 new Department { ID = 20, Name = "HR"       },
                 new Department { ID = 30, Name = "Payroll"  },
             };
     }
 }

Join的使用方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
var joinMS = Employee.GetAllEmployees()         //Outer data source
            .Join(
                Address.GetAllAddress(),        //Inner data source
                employee => employee.AddressId, //Inner key selector
                address => address.ID,          //Outer key selector
                (employee, address) => new      //Projecting data into result set
                {
                    EmployeeName = employee.Name,
                    AddressLine = address.AddressLine
                }
            ).ToList();
var joinQS = (from emp in Employee.GetAllEmployees()
                join address in Address.GetAllAddresses()
                on emp.AddressId equals address.ID
                select new
                {
                    EmployeeName = employee.Name,
                    AddressLine = address.AddressLine
                }).ToList();

多数据源联合查询

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
36
var multipleJoinQS = from emp in Employee.GetAllEmployees()
                    join address in Address.GetAllAddresses()
                    on emp.AddressId equals address.ID
                    join depart in Department.GetAllDepartments()
                    on emp.DepartmentId equals depart.ID
                    select new
                    {
                        ID = emp.ID,
                        EmployeName = emp.Name,
                        DepartmentName = department.Name,
                        AddressLine = address.AddressLine
                    }

var joinMultipleMS = Employee.GetAllEmployees()            //Inner data source
                    .Join(
                        Address.GetAllAddresses(),         //Outter data source
                        empLevel1 => empLevel1.AddressId, // Inner Key
                        addLevel1 => addLevel1.ID,        //Outter Key
                        (empLevel1, addLevel1) => new    
                        {
                            empLevel1, addLevel1
                        }
                    )
                    .Join(
                        Department.GetAllDepartments(),
                        empLevel2 => empLevel2.empLevel1.DepartmentId,
                        depLevel1 => depLevel1.ID,
                        (empLevel2, depLevel1) => new {empLevel2, depLevel1}
                    )
                    .Select(e => new
                    {
                        ID = e.empLevel2.empLevel1.ID,
                        EmployeeName = e.empLevel2.empLevel1.Name,
                        AddressLine = e.empLevel2.addLevel1.AddressLine,
                        DepartmentName = e.depLevel1.Name
                    }).ToList();

译者注：原文中关于Join方法的Method方式这段代码片段是有问题的。实际上Inner Key和Outter Key就是SQL中join中用来连接的两个表中的键。

GroupJoin

GroupJoin用于通过共有的键将多个数据源连接，返回的数据结果以分组方式。

1
2
3
4
5
6
7
8
9
10
11
12
var groupMS = Department.GetAllDepartments()
            .GroupJoin(
                Employee.GetAllEmployees(),
                dept => dept.ID,
                emp => emp.DepartmentId,
                (dept, emp) => new {dept, emp}
            );
var groupQS = from dept in Department.GetAllDepartments()
              join emp in Employee.GetAllEmployees()
              on dept.ID equals emp.DepartmentId
              into EmployeeGroup
              select new {dept, EmployeeGroup};

Left Join

Left Join类似于SQL中的left join,看本文开头的图，左侧表数据将全部输出，而连接的表中如果没有对应的数据则显示空。LINQ中没有现成的left join方法，通过借助GroupJoin 和 SelectMany来实现。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
var LeftOuterJoinQS = from emp in Employee.GetAllEmployees()
                    join addr in Address.GetAllAddresses()
                    on emp.AddressId equals addr.ID
                    into EmployeeGroup
                    from address in EmployeeGroup.DefaultIfEmpty()
                    select new {emp, address};

var LeftOuterJoinMS = Employee.GetAllEmployees()
                    .GroupJoin(
                        Address.GetAllAddresses(),
                        emp => emp.AddressId,
                        addr => addr.ID,
                        (emp, addr) => new {emp, addr}
                    )
                    .SelectMany(
                        x => x.addr.DefaultIfEmpty(),
                        (emp, address) => new {emp, address}
                    );

Cross Join

cross join返回两个集合的笛卡尔积，即第一个集合中的每个元素都会映射到第二个集合的每个元素。下面举例如何使用查询语法和SelectMany方法来实现cross join：

1
2
3
4
5
6
7
8
9
10
11
12
13
var crossJoinMS = Student.GetAllStudnets()
                    .SelectMany(subject => Subject.GetAllSubjects(),
                    (student, sub) => new
                    {
                        Name = student.Name,
                        SubjectName = sub.SubjectName
                    });
var crossJoinQS = from student in Student.GetAllStudnets()
                    from subject in Subject.GetAllSubjects()
                    select new {
                        Name = student.Name,
                        SubjectName = subject.SubjectName
                    };

LINQ中的元素操作

LINQ元素操作用于针对数据源索引来返回单个元素，或者基于断言条件来返回。可以针对一个数据源，或多数据源的查询来返回。主要有以下几个方法来实现元素操作：

  • ElementAt and ElementAtOrDefault 返回指定索引处的元素
  • First and FirstOrDefault 返回第一个符合断言条件的值
  • Last and LastOrDefault 返回最后一个符合断言条件的值
  • Single and SingleOrDefault 返回一个值
  • DefaultEmpty

下面举例各方法的使用：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
List<int> numbers = new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
int QuerySyntax1 = numbers.ElementAt(1);
int QuerySyntax2 = (from num in numbers
                    select num).ElementAtOrDefault(10); //如果使用ElementAt，传入非法索引值，则会引发异常

var first = numbers.First(num => num % 2 == 0);
var fistDefault = numbers.FristOrDefault(num => num > 50);//如果使用First没有找到匹配的值则引发异常
var fristDefaultQuery = (from num in numbers select num).FirstOrDefault();

var last = numbers.Last(num => num % 2 == 0);
var lastDefault = numbers.LastOrDefault(num => num > 50);//如果使用Last没有找到匹配的值则引发异常
var lastDefaultQuery = (from num in numbers select num).LastOrDefault();

int number = numbers.Single(num => num == 20); 
int number = numbers.SingleOrDefault(num => num > 10); //异常，满足条件的超过一个

IEnumerable<int> result = numbers.DefaultIfEmpty();

// List<int> numbers = new List<int>(){};
// IEnumerable<int> result = numbers.DefaultIfEmpty();//返回0
// IEnumerable<int> result = numbers.DefaultIfEmpty(5);//返回5

LINQ中的SequenceEqual

从这个名字就大概能理解这个操作方法的作用，用于比较两个数据源是否相等。两个数据源相等的话，需要数据集合长度一样、每个元素值一样。先来看看SequenceEqual方法的两个签名

1
2
public static bool SequenceEqual<TSource>(this IEnumerable<TSource> first, IEnumerable<TSource> second);
public static bool SquenceEqual<TSource>(this IEnumerable<TSource> first, IEnumerable<TSource> second, IEqualityComparer<TSource> comparer);

看到这两个方法签名，应该比较熟悉了。默认传递两个数据源集合，第二个方法签名，允许传递一个自定义的IEqualityComparer接口，说明处理复杂类数据源比较的时候，需要自己实现比较方法。下面看两个例子：

1
2
3
4
List<string> cityList1 = new List<string> { "Delhi", "Mumbai", "Hyderabad" };
List<string> cityList2 = new List<string> { "Delhi", "Hyderabad", "Mumbai" };
bool IsEqual = cityList1.SequenceEqual(cityList2, StringComparer.OrdinalIgnoreCase);//返回False，顺序不一样
bool isEqual = bool isEqualByOrderBy = cityList1.OrderBy(x => x).SequenceEqual(cityList2.OrderBy(x =>x), StringComparer.OrdinalIgnoreCase); //排序后再比较则返回True

上面例子列举了SequenceEqual的用法，如果数据源中的元素为复杂类，那么进行比较的方法类似于Distinct方法中介绍的4种方法。

扫一扫，分享到微信

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia-plus根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

达文西
毕业于华东师大

高校信息化工程师一枚

dotnet、dotnet core、python、C#
flask、mvc、jquery、workflow、sqlserver、mysql