LINQ查询表达式---------join子句
LINQ查询表达式---------join子句
join 子句接受两个源序列作为输入。 每个序列中的元素都必须是可以与另一个序列中的相应属性进行比较的属性，或者包含一个这样的属性。 join子句使用特殊的 equals 关键字比较指定的键是否相等。 join 子句执行的所有联接都是同等联接。 join 子句的输出形式取决于所执行的联接的具体类型。 以下是三种最常见的联接类型：
内部联接
分组联接
左外部联接
class Program { public class PerInfo { public int Id { get; set; } public string Name { get; set; } public int Age { get; set; } } public class CustInfo { public int PerId { get; set; } public string Tel { get; set; } public string Email { get; set; } } static void Main(string[] args) { List<PerInfo> perinfos = new List<PerInfo> { new PerInfo{Id = 1, Name = "小白", Age = 15}, new PerInfo{Id = 2, Name = "小日", Age = 17}, new PerInfo{Id = 3, Name = "小月", Age = 18}, new PerInfo{Id = 4, Name = "小明", Age = 22} }; List<CustInfo> custInfos = new List<CustInfo> { new CustInfo{PerId = 1, Tel = "153******", Email = "123@qq.com"}, new CustInfo{PerId = 1, Tel = "153****11", Email = "321@qq.com"}, new CustInfo{PerId = 2, Tel = "134******", Email = "112@qq.com"}, new CustInfo{PerId = 2, Tel = "134****11", Email = "211@qq.com"}, new CustInfo{PerId = 4, Tel = "125******", Email = "134@qq.com"}, new CustInfo{PerId = 4, Tel = "125****11", Email = "431@qq.com"} }; //1、内部联接 var query1 = from p in perinfos join c in custInfos on p.Id equals c.PerId select new { NewName = p.Name, NewTel = c.Tel, NewEmail = c.Email }; Console.WriteLine("内部联接："); foreach (var item in query1) { Console.WriteLine(" {0} {1} {2}", item.NewName, item.NewTel, item.NewEmail); } //2、分组联接_01 var query2 = from p in perinfos join c in custInfos on p.Id equals c.PerId into groupquery select new { NewName = p.Name, NewQuery = groupquery }; Console.WriteLine("\n分组联接_01"); foreach (var v in query2) { Console.WriteLine("{0}", v.NewName); foreach (var item in v.NewQuery) { Console.WriteLine(" {0} {1}", item.Tel, item.Email); } } //3、分组联接_02 var query3 = from p in perinfos join c in custInfos on p.Id equals c.PerId into groupquery select groupquery; Console.WriteLine("\n分组联接_02"); foreach (var v in query3) { foreach (var item in v) { Console.WriteLine(" {0} {1} {2}", item.PerId, item.Tel, item.Email); } } //4、分组联接_03 var query4 = from p in perinfos join c in custInfos on p.Id equals c.PerId into groupquery select new { NewName = p.Name, NewQuery = from q in groupquery where q.PerId > 1 orderby q.Email descending select q }; Console.WriteLine("\n分组联接_03"); foreach (var v in query4) { Console.WriteLine("{0}", v.NewName); foreach (var item in v.NewQuery) { Console.WriteLine(" {0} {1} {2}", item.PerId, item.Tel, item.Email); } } //5、左外联接_01 var query5 = from p in perinfos join c in custInfos on p.Id equals c.PerId into groupquery select groupquery.DefaultIfEmpty(new CustInfo() { PerId = p.Id, Tel = "Nothing", Email = "Nothing" }); Console.WriteLine("\n左外联接_01"); foreach (var v in query5) { foreach (var item in v) { Console.WriteLine(" {0} {1} {2}", item.PerId, item.Tel, item.Email); } } // 6、左外联接_02 var query6 = from p in perinfos join c in custInfos on p.Id equals c.PerId into groupquery from item in groupquery.DefaultIfEmpty() select new { Email = item == null ? "Nothing!" : item.Email, ID = p.Id }; Console.WriteLine("\n左外联接_02"); foreach (var v in query6) { Console.WriteLine(" {0} {1} ", v.ID, v.Email); } Console.ReadLine(); } }
输出结果： 内部联接： 小白 153****** 123@qq.com 小白 153****11 321@qq.com 小日 134****** 112@qq.com 小日 134****11 211@qq.com 小明 125****** 134@qq.com 小明 125****11 431@qq.com 分组联接_01 小白 153****** 123@qq.com 153****11 321@qq.com 小日 134****** 112@qq.com 134****11 211@qq.com 小月 小明 125****** 134@qq.com 125****11 431@qq.com 分组联接_02 1 153****** 123@qq.com 1 153****11 321@qq.com 2 134****** 112@qq.com 2 134****11 211@qq.com 4 125****** 134@qq.com 4 125****11 431@qq.com 分组联接_03 小白 小日 2 134****11 211@qq.com 2 134****** 112@qq.com 小月 小明 4 125****11 431@qq.com 4 125****** 134@qq.com 左外联接_01 1 153****** 123@qq.com 1 153****11 321@qq.com 2 134****** 112@qq.com 2 134****11 211@qq.com 3 Nothing Nothing 4 125****** 134@qq.com 4 125****11 431@qq.com 左外联接_02 1 123@qq.com 1 321@qq.com 2 112@qq.com 2 211@qq.com 3 Nothing! 4 134@qq.com 4 431@qq.com
