Linq Join

学生集合：

1 private static List<Student> stu_li = new List<Student>
2 {
3     new Student { UserId="1",Name="Tom",Age=25,DeptId="11"},
4     new Student { UserId="2",Name="Cos",Age=26,DeptId="11"},
5     new Student { UserId="3",Name="Tan",Age=27,DeptId="12"},
6     new Student { UserId="4",Name="Sin",Age=28,DeptId="13"}
7 };

学部集合：

1 private static List<Deptment> dept_li = new List<Deptment>
2 {
3     new Deptment { DeptId="11",DeptName="信息学部",Description="" } 
4 };

=========================================================================
left join
=========================================================================
说明：如果学生中的DeptId在学部中不存在的话，请在代码①行中加上new Deptment()，否则会报错。

 1 public static void LinqLeftJoin()
 2 {
 3     var data_left_join = from stu in stu_li
 4                                  join dept in dept_li on stu.DeptId equals dept.DeptId into depts
 5                                  from dept_tmp in depts.DefaultIfEmpty(new Deptment())//①
 6                                  select new { StudentName = stu.Name, Age = stu.Age, DeptName = dept_tmp.DeptName };
 7 
 8     string str = JsonConvert.SerializeObject(data_left_join);
 9     Console.WriteLine(str);
10 }

结果：

 1 [
 2     {
 3         "StudentName":"Tom",
 4         "Age":25,
 5         "DeptName":"信息学部"
 6     },
 7     {
 8         "StudentName":"Cos",
 9         "Age":26,
10         "DeptName":"信息学部"
11     },
12     {
13         "StudentName":"Tan",
14         "Age":27,
15         "DeptName":null
16     },
17     {
18         "StudentName":"Sin",
19         "Age":28,
20         "DeptName":null
21     }
22 ]

=========================================================================
inner join
=========================================================================

1  public static void LinqInnerJoin()
2  {
3      var data_left_join = from stu in stu_li
4                           join dept in dept_li on stu.DeptId equals dept.DeptId
5                           select new { StudentName = stu.Name, Age = stu.Age, DeptName = dept.DeptName };
6 
7       string str = JsonConvert.SerializeObject(data_left_join);
8       Console.WriteLine(str);
9  }

结果：

 1 [
 2     {
 3         "StudentName":"Tom",
 4         "Age":25,
 5         "DeptName":"信息学部"
 6     },
 7     {
 8         "StudentName":"Cos",
 9         "Age":26,
10         "DeptName":"信息学部"
11     }
12 ]

==========================================================================
cross join
==========================================================================
//在学部集合中新加了一行数据：

1 private static List<Deptment> dept_li = new List<Deptment>
2 {
3      new Deptment { DeptId="11",DeptName="信息学部",Description="" },
4      new Deptment { DeptId="16",DeptName="社科学部",Description="" },
5 };

 1 public static void LinqCrossJoin()
 2 {
 3             var data_left_join = from stu in stu_li
 4                                  from dept in dept_li
 5                                  select new
 6                                  {
 7                                      StudentName = stu.Name,
 8                                      Age = stu.Age,
 9                                      DeptName = dept.DeptName,
10                                      Description = dept.Description
11                                  };
12 
13             string str = JsonConvert.SerializeObject(data_left_join);
14             Console.WriteLine(str);
15 }

结果：

 1 [
 2     {
 3         "StudentName":"Tom",
 4         "Age":25,
 5         "DeptName":"信息学部",
 6         "Description":""
 7     },
 8     {
 9         "StudentName":"Tom",
10         "Age":25,
11         "DeptName":"社科学部",
12         "Description":""
13     },
14     {
15         "StudentName":"Cos",
16         "Age":26,
17         "DeptName":"信息学部",
18         "Description":""
19     },
20     {
21         "StudentName":"Cos",
22         "Age":26,
23         "DeptName":"社科学部",
24         "Description":""
25     },
26     {
27         "StudentName":"Tan",
28         "Age":27,
29         "DeptName":"信息学部",
30         "Description":""
31     },
32     {
33         "StudentName":"Tan",
34         "Age":27,
35         "DeptName":"社科学部",
36         "Description":""
37     },
38     {
39         "StudentName":"Sin",
40         "Age":28,
41         "DeptName":"信息学部",
42         "Description":""
43     },
44     {
45         "StudentName":"Sin",
46         "Age":28,
47         "DeptName":"社科学部",
48         "Description":""
49     }
50 ]

=========================================================================

 1  /// <summary>
 2     /// 学生
 3     /// </summary>
 4     public class Student
 5     {
 6         public string UserId { get; set; }
 7         public string Name { get; set; }
 8         public int Age { get; set; }
 9 
10         public string DeptId { set; get; }
11     }
12 
13     /// <summary>
14     /// 学部
15     /// </summary>
16     public class Deptment
17     {
18         public string DeptId { get; set; }
19         public string DeptName { get; set; }
20         public string Description { get; set; }
21     }