LINQ 基本子句之二 join
Join子句据说可以实现3中连接关系。
1.内部连接——元素的连接关系必须同时满足被连接的两个数据源
2.分组连接
3.左外连接
1.最基本的,内部连接,类似于sql中inner join。
由于student类和phone类只有一个匹配的连接,因此,只返回一个id,1.
public class student { public int id; string name; public student(int id, string name) { this.id = id; this.name = name; } } public class phone { public int id; string number; public phone(int i, string number) { this.id = i; this.number = number; } } class Program { static void Main(string[] args) { List<student> stu=new List<student>(); List<phone> ph=new List<phone>(); student st1=new student (1,"bob"); student st2 = new student(2, "tony"); phone p1 = new phone(1, "2333333"); stu.Add(st1); stu.Add(st2); ph.Add(p1); var result = from x in ph join s in stu on x.id equals s.id select x; foreach(var r in result) Console.WriteLine(r.id); Console.ReadLine(); } }
2.分组连接 类似于full join
注意其中ToList的用法。我还不很明白,Mark之。
public class student { public int id; public string name; public student(int id, string name) { this.id = id; this.name = name; } } public class phone { public int id; public string number; public phone(int i, string number) { this.id = i; this.number = number; } } class Program { static void Main(string[] args) { List<student> stu=new List<student>(); List<phone> ph=new List<phone>(); student st1=new student (1,"bob"); student st2 = new student(2, "tony"); phone p1 = new phone(1, "2333333"); stu.Add(st1); stu.Add(st2); ph.Add(p1); var result = from s in stu join p in ph on s.id equals p.id into g select new { id = s.id, name = s.name, phone = g.ToList() }; foreach (var r in result) { Console.WriteLine(r.id); Console.WriteLine(r.name); if (r.phone.Count > 0) Console.WriteLine(r.phone[0].number); else Console.WriteLine("xxxx"); } Console.ReadLine(); } }
结果如图
3.类似Left join,往往与DefaultIfEmpty()结合使用,若第一个集合中的元素没有找到相关元素,DefaultIfEmpty()可以指定该元素的相关元素的默认元素。
讲phone作为第一个元素
class Program { static void Main(string[] args) { List<student> stu=new List<student>(); List<phone> ph=new List<phone>(); student st1=new student (1,"bob"); student st2 = new student(2, "tony"); phone p1 = new phone(1, "2333333"); stu.Add(st1); stu.Add(st2); ph.Add(p1); var result = from p in ph join s in stu on p.id equals s.id into g from pc in g.DefaultIfEmpty() select new { id = p.id, num = p.number, name = g.ToList() }; foreach (var r in result) { Console.WriteLine(r.id); Console.WriteLine(r.num); if (r.name.Count > 0) Console.WriteLine(r.name[0].name); else Console.WriteLine("xxxx"); } Console.ReadLine(); }
结果如图
通常,若要生成两个集合的左外部连接,可以分两步实现,
其一,使用分组连接执行内部连接。
其二,对分组连接中每个匹配元素序列调用DefaultIfEmpty().