通过16道练习学习Linq和Lambda.docx

1、通过16道练习学习Linq和Lambda通过16道练习学习Linq和Lambda1、 查询Student表中的所有记录的Sname、Ssex和Class列。select sname,ssex,class from studentLinq: from s in Students select new s.SNAME, s.SSEX, s.CLASS Lambda: Students.Select( s = new SNAME = s.SNAME,SSEX = s.SSEX,CLASS = s.CLASS )2、 查询教师所有的单位即不重复的Depart列。select distinct depa

2、rt from teacherLinq: from t in Teachers.Distinct() select t.DEPARTLambda: Teachers.Distinct().Select( t = t.DEPART)3、 查询Student表的所有记录。select * from studentLinq: from s in Students select sLambda: Students.Select( s = s)4、 查询Score表中成绩在60到80之间的所有记录。select * from score where degree between 60 and 80Lin

3、q: from s in Scores where s.DEGREE = 60 & s.DEGREE ( s.DEGREE = 60 & s.DEGREE new Decimal 85,86,88.Contains(s.DEGREE)Not in from s in Scores where !( new decimal85,86,88 ).Contains(s.DEGREE) select sLambda: Scores.Where( s = !(new Decimal85,86,88.Contains(s.DEGREE) Any()应用：双表进行Any时，必须是主键为(String)Cus

4、tomerDemographics CustomerTypeID（String）CustomerCustomerDemos (CustomerID CustomerTypeID)(String)一个主键与二个主建进行Any(或者是一对一关键进行Any)不可，以二个主键于与一个主键进行Anyfrom e in CustomerDemographicswhere !e.CustomerCustomerDemos.Any()select efrom c in Categorieswhere !c.Products.Any()select c6、 查询Student表中95031班或性别为女的同学记录

5、。select * from student where class =95031 or ssex= N女Linq: from s in Students where s.CLASS = 95031 | s.CLASS = 女 select sLambda: Students.Where(s = ( s.CLASS = 95031 | s.CLASS = 女)7、 以Class降序查询Student表的所有记录。select * from student order by Class DESCLinq: from s in Students orderby s.CLASS descending

6、 select sLambda: Students.OrderByDescending(s = s.CLASS)8、 以Cno升序、Degree降序查询Score表的所有记录。select * from score order by Cno ASC,Degree DESCLinq:(这里Cno ASC在linq中要写在最外面) from s in Scores orderby s.DEGREE descending orderby s.CNO ascending select sLambda: Scores.OrderByDescending( s = s.DEGREE) .OrderBy(

7、s = s.CNO)9、 查询95031班的学生人数。select count(*) from student where class = 95031Linq: ( from s in Students where s.CLASS = 95031 select s ).Count()Lambda: Students.Where( s = s.CLASS = 95031 ) .Select( s = s) .Count()10、查询Score表中的最高分的学生学号和课程号。select distinct s.Sno,c.Cno from student as s,course as c ,sco

8、re as sc where s.sno=(select sno from score where degree = (select max(degree) from score)and o = (select cno from score where degree = (select max(degree) from score)Linq: ( from s in Students from c in Courses from sc in Scores let maxDegree = (from sss in Scores select sss.DEGREE ).Max() let sno

9、= (from ss in Scores where ss.DEGREE = maxDegree select ss.SNO).Single().ToString() let cno = (from ssss in Scores where ssss.DEGREE = maxDegree select ssss.CNO).Single().ToString() where s.SNO = sno & c.CNO = cno select new s.SNO, c.CNO ).Distinct()操作时问题？执行时报错： where s.SNO = sno（这行报出来的） 运算符=无法应用于st

10、ring和System.Linq.IQueryable类型的操作数解决：原：let sno = (from ss in Scores where ss.DEGREE = maxDegree select ss.SNO).ToString() Queryable().Single()返回序列的唯一元素；如果该序列并非恰好包含一个元素，则会引发异常。 解：let sno = (from ss in Scores where ss.DEGREE = maxDegree select ss.SNO).Single().ToString()11、查询3-105号课程的平均分。 select avg(de

11、gree) from score where cno = 3-105Linq: ( from s in Scores where s.CNO = 3-105 select s.DEGREE ).Average()Lambda: Scores.Where( s = s.CNO = 3-105) .Select( s = s.DEGREE) .Average()12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 select avg(degree) from score where cno like 3% group by Cno having count(*)=5Linq:

12、 from s in Scores where s.CNO.StartsWith(3) group s by s.CNO into cc where cc.Count() = 5 select cc.Average( c = c.DEGREE)Lambda: Scores.Where( s = s.CNO.StartsWith(3) ) .GroupBy( s = s.CNO ) .Where( cc = ( cc.Count() = 5) ) .Select( cc = cc.Average( c = c.DEGREE) )Linq: SqlMethodlike也可以这样写: s.CNO.S

13、tartsWith(3) or SqlMethods.Like(s.CNO,%3) 13、查询最低分大于70，最高分小于90的Sno列。 select sno from score group by sno having min(degree) 70 and max(degree) cc.DEGREE) 70 & ss.Max( cc = cc.DEGREE) s.SNO) .Where (ss = (ss.Min (cc = cc.DEGREE) 70) & (ss.Max (cc = cc.DEGREE) new sno = ss.Key )14、查询所有学生的Sname、Cno和Degr

14、ee列。 select s.sname,o,sc.degree from student as s,score as sc where s.sno = sc.snoLinq: from s in Students join sc in Scores on s.SNO equals sc.SNO select new s.SNAME, sc.CNO, sc.DEGREE Lambda: Students.Join(Scores, s = s.SNO, sc = sc.SNO, (s,sc) = new SNAME = s.SNAME, CNO = sc.CNO, DEGREE = sc.DEGR

15、EE )15、查询所有学生的Sno、Cname和Degree列。 select sc.sno,ame,sc.degree from course as c,score as sc where o = oLinq: from c in Courses join sc in Scores on c.CNO equals sc.CNO select new sc.SNO,c.CNAME,sc.DEGREE Lambda: Courses.Join ( Scores, c = c.CNO, sc = sc.CNO, (c, sc) = new SNO = sc.SNO, CNAME = c.CNAME, DEGREE = sc.DEGREE )16、查询所有学生的Sname、Cname和Degree列。 select s.sname,ame,sc.degree from student as s,course as c,score as sc where s.sno = sc.sno and o = oLinq: from s in Students from c in Courses from sc in Scores where s.SNO = sc.SNO & c.CNO = sc.CNO select new s.SNAME,c.CNAME,sc.DEGREE 主要参考文章链接：