pkm的linq to Entities学习2
using (testEntities TestEntity = new testEntities()) { #region linq to entities 内容练习2 #region 父子关系表查询 /* var maxScorePerClass = from s in TestEntity.stuinfo group s by s.classID into s1 select new { classid = s1.Key, stuinfo = from s2 in s1 where s2.score == s1.Max(p => p.score) select s2 }; foreach (var sc in maxScorePerClass) //stuinfo 为 IEnumerable<stuinfo> 类型 { Console.WriteLine("classID:{0}", sc.classid); Console.WriteLine("每班最高分数：{0}", sc.stuinfo.First().score); } Console.WriteLine(); */ #endregion #region 查询每班低于平均成绩的学生 /* var LitThanAvgScorePerClass = from s in TestEntity.stuinfo group s by s.classID into s1 select new { classid = s1.Key, stuinfo = from s2 in s1 where s2.score <= s1.Average(p => p.score)//修改此处，可查每班最低成绩、最高成绩等 select s2 }; // Console.Clear(); foreach (var sc in LitThanAvgScorePerClass) //stuinfo 为 IEnumerable<stuinfo> 类型 { Console.WriteLine("每班低于平均分数-classID:{0}", sc.classid); foreach (var stu in sc.stuinfo) { Console.WriteLine("分数：{0}", stu.score); } } Console.WriteLine(); */ #endregion #region 模糊查询---相当于like /* var querylike = from s in TestEntity.stuinfo where s.username.Contains("pkm") //相当于like '%pkm%' select s; foreach (var s in querylike) { Console.WriteLine(s.username); } Console.WriteLine(); */ #endregion #region 编译查询--类似于函数 /* var function = CompiledQuery.Compile((testEntities edm1, string user)//参数 => from s in edm1.stuinfo where s.username == user select s ); var stu001 = function(TestEntity, "pkm001");//调用编译查询 foreach (var s in stu001) { Console.WriteLine(s.username); } Console.WriteLine(); */ #endregion #region any查询，相当于 in /* IQueryable<long> classIDs = TestEntity.classinfo.Select(c => c.classID); var inValues = from s in TestEntity.stuinfo where classIDs.Any(id => id == s.classID) select s; foreach (var s in inValues) { Console.WriteLine(s.username); } Console.WriteLine(); */ #endregion #region 插入数据--相当于insert /* var newStudent = new stuinfo() { username = "test0003", userpwd = "test0003", score = 95, age = 30, classID = 2, CreateTime = DateTime.Now }; TestEntity.AddTostuinfo(newStudent); //TestEntity.AddObject("stuinfo", newStudent); //这句的上面一句是一样的 TestEntity.SaveChanges(); Console.Write("添加数据成功！"); */ #endregion #region 修改数据 -- update /* var toModify = (from s in TestEntity.stuinfo where s.id == 9 select s).FirstOrDefault(); toModify.userpwd = "test"; TestEntity.SaveChanges(); //修改之后进行保存就行了。 */ #endregion #region 删除数据 delete /* var toDelete = (from s in TestEntity.stuinfo where s.id == 10 select s).FirstOrDefault(); TestEntity.DeleteObject(toDelete); TestEntity.SaveChanges(); */ #endregion #region 查询结果转换为 List,Array,Dictionary var tempstudents = from s in TestEntity.stuinfo where s.CreateTime.Value.Year == 2012 select s; //stuinfo[] array = tempstudents.ToArray(); //List<stuinfo> list = tempstudents.ToList(); Dictionary<long, stuinfo> dict = tempstudents.ToDictionary(s => s.id); foreach (var s in dict.Values) { Console.WriteLine("用户：{0},密码：{1}", s.username, s.userpwd); } Console.WriteLine(); #endregion #endregion }