linq复习

向右看齐 2022-08-11 00:56 200阅读 0赞

/// <summary>
 /// join多对多链接
 /// </summary>
 public void Test6()
 {
 var namelist = new[] { 
 new{id = "1", name = "Jon"},
 new{id = "2", name = "Holly"},
 new{id = "1", name = "Tom"},
 new{id = "3", name = "Tom"},
 new{id = "3", name = "Tom"}
 };
 var dtlist = new[] { 
 new{id = "2", age = 23, },
 new{id = "2", age = 28, },
 new{id = "3", age = 26},
 new{id = "1", age = 25}
 };
 var query = from n in namelist
 join d in dtlist
 on n.id equals d.id into tempdt
 select new
 {
 
 };
 //...
 }
 
 /// <summary>
 /// 自联接关系
 /// </summary>
 public void Test8()
 {
 //会先做笛卡尔积，然后再筛选，比较消耗
 //var q = from e1 in db.Employees
 // from e2 in e1.Employees
 // where e1.City == e2.City
 // select new
 // {
 // name1 = e1.FirstName + e1.LastName,
 // name2 = e2.FirstName + e2.LastName,
 // e1.City
 // };
 }
 /// <summary>
 /// 一对多=> 双向联接(Two way join)
 /// </summary>
 /// 
 public void Test9()
 {
 //var q = from c in db.Customers
 // join o in db.Orders on c.CustomerID
 // equals o.CustomerID into orders
 // select new
 // {
 // c.ContactName,
 // OrderCount = orders.Count()
 // };
 /*在一对多关系中，左边是1，它每条记录为c（from c in db.Customers），
 * 右边是Many，其每条记录叫做o ( join o in db.Orders )，
 * 每对应左边的一个c，就会有一组o，那这一组o，就叫做orders，也就是说，
 * 我们把一组o命名为orders，这就是into用途。这也就是为什么在select语句中，orders可以调用聚合函数Count。
 */
 }
 
 /// <summary>
 /// join 一对一链接
 /// </summary>
 public void Test5()
 {
 var str = new[] { 
 new{id = "1", name = "Jon"},
 new{id = "2", name = "Holly"},
 new{id = "3", name = "Tom"}
 };
 var str2 = new[] { 
 new{id = "2", age = 23},
 new{id = "3", age = 26},
 new{id = "1", age = 25}
 };
 var query = from n in str
 join a in str2
 on n.id equals a.id into tempa
 from ta in tempa.DefaultIfEmpty()
 select new
 {
 n,
 ta
 };
 foreach (var item in query)
 {
 Response.Write(string.Format("name:{0}, age:{1} ", item.n.name, item.ta.age.ToString()));
 /*
 name:Jon, age:25
 name:Holly, age:23
 name:Tom, age:26
 */
 }
 
 //每个类别中单价最低的产品
 //var categories = from p in db.Products
 // group p by p.CategoryID into g
 // select new
 // {
 // CategoryID = g.Key,
 // CheapestProducts = from p2 in g
 // where p2.UnitPrice == g.Min(x => x.UnitPrice)
 // //每个类别中单价高于该类别平均单价的产品
 // //where p2.UnitPrice == g.Average(x = x.UnitPrice) 
 // select p2
 // };
 }
 
 /// <summary>
 /// join、一对多链接
 /// </summary>
 public void Test4()
 {
 //定义匿名类型
 // var str = new { id = "1", name = "jon" };
 
 //定义匿名类型数组
 var str = new[] { 
 new{id = "1", name = "Jon"},
 new{id = "2", name = "Holly"},
 new{id = "3", name = "Tom"}
 };
 var str2 = new[] { 
 new{id = "1", name = "Robin"},
 new{id = "2", name = "William"},
 new{id = "2", name = "kkk"},
 new{id = "3", name = "jjjj"}
 };
 
 //一对多情况
 //var query = from a in str
 // join b in str2 on a.id equals b.id
 // select new { Aname = a.name, Bname = b.name };
 //foreach (var item in query)
 //{
 // Response.Write(string.Format("Aname:{0}, Bname:{1}", item.Aname, item.Bname) + " ");
 // /*
 // Aname:Jon, Bname:Robin
 // Aname:Holly, Bname:William
 // Aname:Holly, Bname:kkk
 // Aname:Tom, Bname:jjjj
 // */
 //}
 var query = from a in str
 join b in str2 on a.id equals b.id into temp2
 select new
 {
 a,
 temp2
 };
 foreach (var item in query)
 {
 Response.Write(string.Format("Aname:{0}, =>Bname Count:{1}", item.a.name, item.temp2.Count()) + " ");
 /*
 Aname:Jon, =>Bname Count:1
 Aname:Holly, =>Bname Count:2
 Aname:Tom, =>Bname Count:1
 */
 }
 }
 
 /// <summary>
 /// group by测试
 /// </summary>
 public void Test3()
 {
 char[] gitem = { 'a', 'b', 'c', 'f' }; //首字母分组对象集合
 string[] str = { "aa", "bb", "cc", "dd", "fine" };
 
 //var query = from s in str
 // group s by s[0] into ws //针对范围变量s，用s[0]分组
 // where gitem.Contains(ws.Key)
 // orderby ws.Key ascending
 // select ws.Key;
 var q = from s in str
 where gitem.Contains(s[0])
 select s;
 
 //foreach (var item in query)
 foreach (var item in q)
 {
 //Response.Write(string.Format("{0}, ", item));
 /*
 a,
 b,
 c,
 f,
 */
 Response.Write(string.Format("{0}, ", item));
 
 }
 }
 
 /// <summary>
 /// let测试
 /// </summary>
 public void Test2()
 {
 string[] strings = { "A penny saved is a penny earned.", "The aaxly sdj", "the pa is no" };
 //选出单词首字母以a和e开头的，不区分大小写
 var g = from j in strings
 let w = j.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
 from k in w
 let nw = k.ToLower()
 where nw[0] == 'a' || nw[0] == 'e'
 select k;
 
 foreach (var item in g)
 {
 Response.Write(string.Format("{0}, ", item));
 }
 }
 
 /// <summary>
 /// 自链接
 /// </summary>
 public void Test1()
 {
 List<Student> students = new List<Student> 
 { 
 new Student 
 {
 LastName="xiaogui",Scores=new List<int>{97,42,91,60}
 },
 new Student
 {
 LastName="xiaozhan",Scores=new List<int>{50,92,81,60}
 }, 
 new Student 
 {
 LastName="xiaolan",Scores=new List<int>{32,32,81,90}
 },
 new Student 
 {
 LastName="xiaowan",Scores=new List<int>{92,22,81,60}
 }, 
 };
 
 //选出分数中大于90的学生
 var q = from s in students
 from o in s.Scores
 where o > 90
 select new
 {
 lastname = s.LastName,
 o
 };
 foreach (var item in q)
 {
 Response.Write(string.Format("{0}:{1} ", item.lastname, item.o));
 /*Note：xiaogui出现了2次
 * 
 xiaogui:97
 xiaogui:91
 xiaozhan:92
 xiaowan:92
 */
 }
 }
 
 }//testlinq
 
 public class Student
 {
 public string LastName { get; set; }
 public List<int> Scores { get; set; }
 }
 public class Employee
 {
 public string First { get; set; }
 public string Last { get; set; }
 public int ID { get; set; }
 }