/*********************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,"黄波");