linq，lambda

linq

1. 什么是linq？
LINQ(Language Integrated Query)语言集成查询是一组用于c#和Visual Basic语言的扩展。它允许编写C#或者Visual Basic代码以操作内存数据的方式，查询数据库。linq360百科
2. 使用linq的优点：
1、无需复杂学习过程即可上手
2、编写更少代码即可创建完整应用。
3、更快开发错误更少的应用程序。
4、无需求助奇怪的编程技巧就可合并数据源。
5、能够大幅减少过程控制语句的代码块，使代码的可读性和可维护性大幅提高。
6、任何对象或数据源都可以定制实现Linq适配器，为数据交互带来真正方便。

3. 普通查询语句和linq以及lambda表达式下的查询对比
LINQ的书写格式如下： 　　
from 临时变量 in 集合对象或数据库对象 　　
where 条件表达式 　　
[order by条件] 　　
select 临时变量中被查询的值 　　
[group by 条件]
Lambda表达式的书写格式如下：
(参数列表) => 表达式或者语句块其中： 参数个数：可以有多个参数，一个参数，或者无参数。
1 查询学生表的全部记录
select * from student
linq:
from s in Students
select s
lanmbda
Students.Selsect(s=>s)
2 查询Student 表中的所有sname sex class 记录
select sname sex class from student
linq:
from s in Students
select new {s.sname,s.sex,s.class}
lambda:
Student.Selsect（s=>new{sname= s.sname,sex = s.sex,class = s.class}）
3 distinct去重的查询查询教师所有的的单位不重复depart列
select distinct depart from teacher
linq:
from t in Teachers.Distinct()select t.depart
lambda:
Teachers.Distinct.Select(t=>t.depart)

4 连接查询 between and 查询所有学生成绩在60到80之间的所有记录
select s from student where score between 60 and 80
linq:
from s in Studnetswhere s.score>=60&& s.score<80select s
lambda:
Student.where( s=>s.score<=60&&s.score<80)
5 在范围内筛选 in
select * from student where score in (87,88,89)
linq:
from s in Studnetswhere (new decima[]{87,88,89}).Contain(s.score)select s
lambda:
Score.where(s=>new Decima[]{87,88,89}.Contain(s.score))
6 or条件过滤
select * from student where calss='01’or sex=‘女’
linq:
from s in student where s.class =‘01’||s.sex='女’select s
lambda:
Students.where(s=>(s.class=‘01’||s.sex=‘女’))

7 排序以class降序查询Studnet表为例查询所有记录
select * from student where order by class desc
linq:
from s in studentorder by s.class descendingselect s
lambda:
Students.OrderByDescending(s=>s.class)
8 count()行数查询
select count(*) from student where score=‘85’
linq:
(from s in Studentswhere s.score='85’selects).count()
lambda:
Students.Where(s=>s.score=‘85’).Select(s=>s).count()
9.avg()平均实例 Code查询’3-105’号课程的平均分。
select avg(degree) from score where cno = ‘3-105’
Linq:
(from s in Scoreswhere s.CNO == "3-105"select s.DEGREE).Average()
Lambda:
Scores.Where( s => s.CNO == “3-105”).Select( s => s.DEGREE)

10.子查询实例 Code查询Score表中的最高分的学生学号和课程号。
select distinct s.Sno,c.Cno
from student as s,course as c ,score as sc
where s.sno=(select sno from score where degree = (select max(degree) from score))
and c.cno = (select cno from score where degree = (select max(degree) from score))
Linq:
(from s in Studentsfrom c in Coursesfrom sc in Scoreslet maxDegree = (from sss in Scoresselect sss.DEGREE).Max()let sno = (from ss in Scoreswhere ss.DEGREE == maxDegreeselect ss.SNO).Single().ToString()
let cno = (from ssss in Scoreswhere ssss.DEGREE == maxDegreeselect ssss.CNO).Single().ToString()where s.SNO == sno && c.CNO == cnoselect new {s.SNO,c.CNO}).Distinct()

11.分组 过滤实例 Code查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。select avg(degree) from score where cno like ‘3%’ group by Cno having count(*)>=5
Linq:
from s in Scoreswhere s.CNO.StartsWith(“3”)group s by s.CNOinto ccwhere cc.Count() >= 5select 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.StartsWith(“3”) or SqlMethods.Like(s.CNO,"%3")

12.分组实例 Code查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(degree) from score where cno like ‘3%’ group by Cno having count(*)>=5
Linq:
from s in Scoreswhere s.CNO.StartsWith(“3”)group s by s.CNOinto ccwhere cc.Count() >= 5select 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.StartsWith(“3”) or SqlMethods.Like(s.CNO,"%3")

13. 多表查询实例 Codeselect sc.sno,c.cname,sc.degree from course as c,score as sc where c.cno = sc.cno
    Linq:
    from c in Coursesjoin sc in Scoreson c.CNO equals sc.CNOselect 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}).Average()