LINQ的书写格式如下:
from 临时变量 in 集合对象或数据库对象
where 条件表达式
[order by条件]
select 临时变量中被查询的值
[group by 条件]
Lambda表达式的书写格式如下:
(参数列表) => 表达式或者语句块
其中: 参数个数:可以有多个参数,一个参数,或者无参数。
参数类型:可以隐式或者显式定义。
表达式或者语句块:这部分就是我们平常写函数的实现部分(函数体)。
1.查询全部
实例 Code
查询Student表的所有记录。select * fromstudent
Linq:from s inStudentsselects
Lambda:
Students.Select( s=> s)
2 按条件查询全部:
实例 Code
查询Student表中的所有记录的Sname、Ssex和Class列。select sname,ssex,class fromstudent
Linq:from s inStudentsselect new{
s.SNAME,
s.SSEX,
s.CLASS
}
Lambda:
Students.Select( s=> new{
SNAME= s.SNAME,SSEX = s.SSEX,CLASS =s.CLASS
})
3.distinct 去掉重复的
实例 Code
查询教师所有的单位即不重复的Depart列。select distinct depart fromteacher
Linq:from t inTeachers.Distinct()selectt.DEPART
Lambda:
Teachers.Distinct().Select( t=> t.DEPART)
4.连接查询 between and
实例 Code
查询Score表中成绩在60到80之间的所有记录。select * from score where degree between 60 and 80Linq:from s inScoreswhere s.DEGREE >= 60 && s.DEGREE < 80
selects
Lambda:
Scores.Where(
s=>(
s.DEGREE>= 60 && s.DEGREE < 80)
)
5.在范围内筛选 In
实例 Codeselect * from score where degree in (85,86,88)
Linq:from s inScoreswhere(new decimal[]{85,86,88}
).Contains(s.DEGREE)selects
Lambda:
Scores.Where( s=> new Decimal[] {85,86,88}.Contains(s.DEGREE))
6.or 条件过滤
实例 Code
查询Student表中"95031"班或性别为"女"的同学记录。select * from student where class ='95031' or ssex= N'女'Linq:from s inStudentswhere s.CLASS == "95031"
|| s.CLASS == "女"
selects
Lambda:
Students.Where(s=> ( s.CLASS == "95031" || s.CLASS == "女"))
7.排序
实例 Code
以Class降序查询Student表的所有记录。select * fromstudent order by Class DESC
Linq:from s inStudentsorderbys.CLASS descendingselects
Lambda:
Students.OrderByDescending(s=> s.CLASS)
8.count()行数查询
实例 Codeselect count(*) from student where class = '95031'Linq:
(from s inStudentswhere s.CLASS == "95031"
selects
).Count()
Lambda:
Students.Where( s=> s.CLASS == "95031")
.Select( s=>s)
.Count()
10.avg()平均
实例 Code
查询'3-105'号课程的平均分。select avg(degree) from score where cno = '3-105'Linq:
(from s inScoreswhere s.CNO == "3-105"
selects.DEGREE
).Average()
Lambda:
Scores.Where( s=> s.CNO == "3-105")
.Select( s=> s.DEGREE)
11.子查询
实例 Code
查询Score表中的最高分的学生学号和课程号。select distinct s.Sno,c.Cno from student as s,course as c ,score asscwhere s.sno=(select sno from score where degree = (select max(degree) fromscore))
and c.cno= (select cno from score where degree = (select max(degree) fromscore))
Linq:
(from s inStudentsfrom c inCoursesfrom sc inScores
let maxDegree= (from sss inScoresselectsss.DEGREE
).Max()
let sno= (from ss inScoreswhere ss.DEGREE ==maxDegreeselectss.SNO).Single().ToString()
let cno= (from ssss inScoreswhere ssss.DEGREE ==maxDegreeselectssss.CNO).Single().ToString()where s.SNO == sno && c.CNO ==cnoselect new{
s.SNO,
c.CNO
}
).Distinct()
12.分组 过滤
实例 Code
查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。select avg(degree) from score where cno like '3%' group by Cno having count(*)>=5Linq:from s inScoreswhere s.CNO.StartsWith("3")
group s by s.CNO
into ccwhere 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: SqlMethod
like也可以这样写:
s.CNO.StartsWith("3") or SqlMethods.Like(s.CNO,"%3")
13.分组
实例 Code
查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。select avg(degree) from score where cno like '3%' group by Cno having count(*)>=5Linq:from s inScoreswhere s.CNO.StartsWith("3")
group s by s.CNO
into ccwhere 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: SqlMethod
like也可以这样写:
s.CNO.StartsWith("3") or SqlMethods.Like(s.CNO,"%3")
14. 多表查询
实例 Codeselect sc.sno,c.cname,sc.degree from course as c,score as sc where c.cno =sc.cno
Linq:from c inCourses
join scinScores
on 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()