Linq无聊练习系列6--Any/All/Contains/Concat/Union/Intersect/Except/take/skip/SqlMethods操作练习

浏览数：34 / 时间：2015年06月12日

/*********************Any/All/Contains/Concat/Union/Intersect/Except/take/skip/SqlMethods操作练习**********************************/
            //判断没有成绩的学生
            var list = from s in ctx.T_Student
                       where !s.T_Score.Any()
                       select s;
            //判断有成绩大于80的学生
            var list1 = (from s in ctx.T_Student
                         where s.T_Score.Any(t => t.score > 80)
                         select s).Distinct();
            //所有科目成绩大于80的学生
            var list2 = from s in ctx.T_Student
                        where s.T_Score.All(t=>t.score>80)
                        select s;
            //查询黄阳是否参加过考试
            string[] names = { "黄阳"};
            var list4 = from s in ctx.T_Score
                        where names.Contains(s.T_Student.stuName)
                        select s;
            //查询黄阳是否参加过考试
            var list3 = from s in ctx.T_Score
                        where (new string[] { "黄阳" }).Contains(s.T_Student.stuName)
                        select s;
            //参加过考试但是不是黄阳的同学
            var list5 = from s in ctx.T_Score
                        where !(new string[] { "黄阳" }).Contains(s.T_Student.stuName)
                        select s;
            //contains也可以包含一个对象，如查询那些学生参加了课程编号为001的第一个同学，对于contains只能包含一个实体，不能保护实体集
            var temp1 = (from s in ctx.T_Score
                        where s.stuNumber == "001"
                        select s).First();
            var result = ctx.T_Student.Where(p=>p.T_Score.Contains(temp1));
            //查找黄阳和黄波的所有成绩
            string[] names1 = {"黄阳","黄波"};
            var result2 = ctx.T_Score.Where(f=>names1.Contains(f.T_Student.stuName));
            //或者也可以这样
            var result3 = from s in ctx.T_Score
                          where (new string[] { "黄阳", "黄波" }).Contains(s.T_Student.stuName)
                          select s;
            //对于concat，连接不同的集合，不会自动过滤相同项,而且它只能对于单列进行连接，对多列进行连接，会报语法错误，因为没有相应的扩展方法

            var result4 = (from s in ctx.T_Student
                           select s.stuName)
                          .Concat(from k in ctx.T_Score
                                  select k.T_Cource.courceName );
            //合并所有的学生
            var result5 = ((from s in ctx.T_Student
                            select s.stuNumber)
                        .Union
                        (from k in ctx.T_Score
                         select k.stuNumber)).Distinct();
            //交集
            var result6 = (from s in ctx.T_Student
                           select s.stuNumber).Intersect
                          (from k in ctx.T_Score
                               select k.stuNumber);
            //差集
            var result7 = (from s in ctx.T_Student
                           select s.stuNumber).Intersect
                               (from k in ctx.T_Score
                                select k.stuNumber);
            //查询前三个学生的信息
            var result8 = (from s in ctx.T_Student
                          select s).Take(3);
            //查询三个学生以外的所有学生信息
            var result9 = (from s in ctx.T_Student
                               select s).Skip(3);
            //当学号为2091723时候，就停止获取学生信息
            var result10 = (from s in ctx.T_Student
                            select s).TakeWhile(k => k.stuNumber == "2091723");
            //当学号为2091723时候，就停止跳过学生信息，并获取后边的所有学生信息
            var result11 = (from s in ctx.T_Student
                            select s).SkipWhile(k => k.stuNumber == "2091723");
            //可以用skip和take来对数据进行分页，这非常方便

            //查看姓名以黄开头的学生信息,这时候程序需要引入using System.Data.Linq.SqlClient;命名空间
            var result12 = from s in ctx.T_Student
                           where SqlMethods.Like(s.stuName,"黄%")
                           select s;
            //查询学号为2X917X2的学生
            var result13 = from s in ctx.T_Student
                           where SqlMethods.Like(s.stuNumber, "2_917_2")
                           select s;
            //比较时间的操作分别有：DateDiffDay、DateDiffHour、DateDiffMillisecond、DateDiffMinute、DateDiffMonth、DateDiffSecond、DateDiffYear ，也是sqlMethods方法，而数据库中的表并没有设计时间的字段，所以就不演示了

            //SQL语句进行编辑重新查询,需要引用命名空间using System.Data.Linq;
            var fn = CompiledQuery.Compile(
                (DB_StudentDataContext ctx1,string stuName)=>
                from s in ctx1.T_Student
                where s.stuName==stuName
                select s
                );
            var data1 = fn(ctx,"黄阳");
            var data2 = fn(ctx,"黄波");