EF中的三种linq、lambda、sql查询方式

最新推荐文章于 2024-03-17 16:38:40 发布

qq_43786444

最新推荐文章于 2024-03-17 16:38:40 发布

阅读量1.1k

点赞数 1

分类专栏： .NET EF

.NET 同时被 2 个专栏收录

15 篇文章

订阅专栏

5 篇文章

订阅专栏

sql 、linq、lambda 查询语句的区别

转：https://blog.youkuaiyun.com/weixin_39550799/article/details/78918301
LINQ的书写格式如下：
from 临时变量 in 集合对象或数据库对象
where 条件表达式
[order by条件]
select 临时变量中被查询的值
[group by 条件]

Lambda表达式的书写格式如下：
(参数列表) => 表达式或者语句块
其中：参数个数：可以有多个参数，一个参数，或者无参数。
参数类型：可以隐式或者显式定义。

表达式或者语句块：这部分就是我们平常写函数的实现部分（函数体）。

1.查询全部

[sql] view plain copy

查询Student表的所有记录。
select * from student
Linq:
```
 from s in Students  
```
```
 select s  
```
Lambda:
```
 Students.Select( s => s)  
```

2 按条件查询全部：

查询Student表中的所有记录的Sname、Ssex和Class列。

select sname,ssex,class from student

[sql] view plain copy

Linq:
```
 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

```
 })  
```

3.distinct 去掉重复的

[sql] view plain copy

查询教师所有的单位即不重复的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

[sql] view plain copy

查询Score表中成绩在60到80之间的所有记录。
select * from score where degree between 60 and 80
Linq:
```
 from s in Scores  
```

 where s.DEGREE >= 60 && s.DEGREE < 80

```
 select s  
```
Lambda:
```
 Scores.Where(  
```
```
     s => (  
```

             s.DEGREE >= 60 && s.DEGREE < 80

```
          )  
```
```
 )  
```

5.在范围内筛选 In

[sql] view plain copy

select * from score where degree in (85,86,88)
Linq:
```
 from s in Scores  
```
```
 where (  
```
```
         new decimal[]{85,86,88}  
```
```
       ).Contains(s.DEGREE)  
```
```
 select s  
```
Lambda:

 Scores.Where( s => new Decimal[] {85,86,88}.Contains(s.DEGREE))

6.or 条件过滤

[sql] view plain copy

查询Student表中"95031"班或性别为"女"的同学记录。
select * from student where class =‘95031’ or ssex= N’女’
Linq:
```
 from s in Students  
```
```
 where s.CLASS == "95031"  
```
```
    || s.CLASS == "女"  
```
```
 select s  
```
Lambda:

 Students.Where(s => ( s.CLASS == "95031" || s.CLASS == "女"))

7.排序

[sql] view plain copy

以Class降序查询Student表的所有记录。
select * from student order by Class DESC
Linq:
```
 from s in Students  
```
```
 orderby s.CLASS descending  
```
```
 select s  
```
Lambda:

 Students.OrderByDescending(s => s.CLASS)

8.count（）行数查询

[sql] view plain copy

select count(*) from student where class = ‘95031’
Linq:
```
 (    from s in Students  
```
```
     where s.CLASS == "95031"  
```
```
     select s  
```
```
 ).Count()  
```
Lambda:

 Students.Where( s => s.CLASS == "95031" )

```
             .Select( s => s)  
```
```
                 .Count()  
```

10.avg()平均

[sql] view plain copy

查询’3-105’号课程的平均分。
select avg(degree) from score where cno = ‘3-105’
Linq:
```
 (  
```
```
     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)  
```

11.子查询

[sql] view plain copy

查询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 Students  
```
```
     from c in Courses  
```
```
     from sc in Scores  
```

     let maxDegree = (from sss in Scores

                     select sss.DEGREE

```
                     ).Max()  
```
```
     let sno = (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()  
```

12.分组过滤

[sql] view plain copy

查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(degree) from score where cno like ‘3%’ group by Cno having count(*)>=5
Linq:
```
     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: SqlMethod
like也可以这样写:

 s.CNO.StartsWith("3") or SqlMethods.Like(s.CNO,"%3")

13.分组

[sql] view plain copy

查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(degree) from score where cno like ‘3%’ group by Cno having count(*)>=5
Linq:
```
     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: SqlMethod
like也可以这样写:

 s.CNO.StartsWith("3") or SqlMethods.Like(s.CNO,"%3")

多表查询

[sql] view plain copy

Linq:
```
 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

})

```
             .Average()  
```