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 删除前不查询，只指定主键的值
                  var deleteStu = new stuinfo
                {
                id = 9
                };
                MyEntity.AttachTo("stuinfo", deleteStu);
                MyEntity.DeleteObject(deleteStu);
                MyEntity.SaveChanges();//如果有此主键则删除，否则将出异常。要使用try…catch…包含
                  #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
            }