sql 、linq、lambda 查询语句的区别
LINQ的书写格式如下：
from 临时变量 in 集合对象或数据库对象
where 条件表达式
[order by条件]
select 临时变量中被查询的值
[group by 条件]
Lambda表达式的书写格式如下：
(参数列表) => 表达式或者语句块
其中： 参数个数：可以有多个参数，一个参数，或者无参数。
参数类型：可以隐式或者显式定义。
表达式或者语句块：这部分就是我们平常写函数的实现部分（函数体）。
查询操作符：http://blog.csdn.net/anchenyanyue/article/details/6732166
·Distinct - 过滤集合中的相同项；延迟 ·Union - 连接不同集合，自动过滤相同项；延迟 ·Concat - 连接不同集合，不会自动过滤相同项；延迟 ·Intersect - 获取不同集合的相同项（交集）；延迟 ·Except - 从某集合中删除其与另一个集合中相同的项；延迟 ·Skip - 跳过集合的前n个元素；延迟 ·Take - 获取集合的前n个元素；延迟 ·SkipWhile - 直到某一条件成立就停止跳过；延迟 ·TakeWhile - 直到某一条件成立就停止获取；延迟 ·Single - 根据表达式返回集合中的某一元素；不延迟 ·SingleOrDefault - 根据表达式返回集合中的某一元素（如果没有则返回默认值）；不延迟 ·Reverse - 对集合反向排序；延迟 ·SelectMany - Select选择（一对多）；延迟
1.查询全部
实例 Code 查询Student表的所有记录。 select * from student Linq: from s in Students select s Lambda: Students.Select( s => s)
2 按条件查询全部：
实例 Code 查询Student表中的所有记录的Sname、Ssex和Class列。 select sname,ssex,class from student 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 去掉重复的
实例 Code 查询教师所有的单位即不重复的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
实例 Code 查询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
实例 Code 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 条件过滤
实例 Code 查询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.排序
实例 Code 以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（）行数查询
实例 Code 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()
9.avg()平均
实例 Code 查询'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)
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 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()
11.分组 过滤
实例 Code 查询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")
12.分组
实例 Code 查询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. 多表查询
实例 Code select sc.sno,c.cname,sc.degree from course as c,score as sc where c.cno = sc.cno 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()
14. null
在C#中，存在一种特殊的类型，Nullable的类型，这个类型可以为空的状态。 在数据库中，如果类型允许为空，就会有NULL的字段 在Lamda查询表达式中，对于数据库中为空的情况也要特殊的注意一下 在SQL的查询中，如果存在为空的字段，例如 ------------------------------------ table Test ------------------------------------ A B C 1 NULL 2 2 true 2 3 false 2 ------------------------------------ 我们要查询 不为真的情况，我们用 select Count(*) from Test where B<>true 此时查询的结果是只有一条 ------------------------------------ A B C 3 false 2 ------------------------------------ 如果按照正常的逻辑来考虑，应该是2条的，如下 ------------------------------------ A B C 1 NULL 2 3 false 2 ------------------------------------ 但是由于Null字段的特殊性，在查询的时候为 select Count(*) from Test where B is Null 才能够查询到 ------------------------------------ A B C 1 NULL 2 ------------------------------------ 这条记录，因此我们要查询这样的数据，就不能够用不等于进行查询，要用或者来进行查询 select Count(*) from Test where B is Null or B=false 在Linq的查询中同理，我们用Lamd表达式查询，如果为三态的情况，我们想要其中的另两个结果，此时也要用or来进行连接 如： Tests 为表Test的集合 Tests.where(p=>p.B!=true) 这样查询时不会得到正确的结果的，原因很简单，就是因为Null的字段不会自动翻译 因此正确的查询结果是 Tests.where(p=>p.B==false||p.B==null) 此时查询就为正确的结果，当然我们可以利用这种特性，查询的时候可以查询所有的有效数据，避免掉那些为空的数据。 OK，关于Lambda查询和SQL查询 字段为空（NULL）的情况要注意了.
sql:ShippedDate IS NULL linq: where o.ShippedDate==null lamda: (o => (o.ShippedDate == (DateTime?)null)) sql:ShippedDate IS NULL linq: Nullable<DateTime>.Equals(o.ShippedDate,null) lamda: (o => Object.Equals (o.ShippedDate, null)) sql:ShippedDate IS NULL linq: o.ShippedDate.Value==(DateTime?)null lamda: (o => ((DateTime?)(o.ShippedDate.Value) == (DateTime?)null)) sql:ShippedDate IS NULL linq: System.Data.Linq.SqlClient.SqlMethods.Equals(o.ShippedDate.Value,null) lamda:(o => Object.Equals (o.ShippedDate.Value, null)) sql:NOT ([t0].[ShippedDate] IS NOT NULL) linq: !o.ShippedDate.HasValue lamda: (o => !(o.ShippedDate.HasValue))
15.分组获取前几条
var lstCheckReportQuestionId = db.TCheckReportQuestion.Where(m => m.RID == gRid).Select(m => m.QuestionID); var lstImage = from a in db.TInspectQuestionImage where lstCheckReportQuestionId.Contains(a.QuestionID) select a; //lamda: var firstImage = lstImage.GroupBy(m => new { m.QuestionID }).SelectMany(m => m.OrderBy(p => p.ImageID).Take(1)).Select(m => new { a1 = m.ImageID, a2 = m.QuestionID, a3 = m.MiniImgUrl }).ToList(); //linq: var lstInspectQuestionImage = (from a in db.TInspectQuestionImage where lstCheckReportQuestionId.Contains(a.QuestionID) group a by a.QuestionID into g select new { QuestionID = g.Key, Source = g.OrderBy(m => m.ImageID).Take(1) }) .Select(m => new { QuestionID = m.QuestionID, MiniImgUrl = m.Source.Select(p => p.MiniImgUrl) }).ToList();
16.空字段使用contains查询的解决办法
Name允许为空且在数据库中存在空值（NULL），使用contains会出现常见的报错 原理：Contains关键字的工作原理是返回一个bool值，当满足contains条件时返回true，否则返回false，以此来决定本条数据是否要获取，所以判断字段为空就直接用false来返回来跳过执行contains； 用法如下：var userlist= (IEnumerable<User>)data.Where(t => (string.IsNullOrWhiteSpace(t.Name) ? false : t.Name.Contains(querystring));
17.case when
1.select中使用case when
linq代码： Products.Select(P => new { ID = P.ProductID, Name = P.Name, Color = P.Color, Price = (P.Color == "Red" ? P.StandardCost : (P.Color == "Black" ? P.StandardCost + 10 : P.ListPrice)) }); sql原型： SELECT ProductID, Name, Color, CASE WHEN Color = 'Red' THEN StandardCost WHEN Color = 'Black' THEN StandardCost + 10 ELSE ListPrice END Price FROM SalesLT.Product
2.where中使用case when
linq代码： Products .Where(P => (P.Color == "Red" ? (P.StandardCost > 100) : (P.Color == "Black" ? P.ListPrice > 100 : P.ListPrice == P.StandardCost))) .Select(P => new { ID = P.ProductID, Name = P.Name, Color = P.Color, StandardCost = P.StandardCost, ListPrice = P.ListPrice }); sql原型： SELECT ProductID, Name, Color, StandardCost, ListPrice FROM SalesLT.Product WHERE ( (CASE WHEN Color = 'Red' THEN (CASE WHEN StandardCost > 100 THEN 1 WHEN NOT (StandardCost > 100) THEN 0 ELSE NULL END) WHEN Color = 'Black' THEN (CASE WHEN ListPrice > 100 THEN 1 WHEN NOT (ListPrice > 100) THEN 0 ELSE NULL END) ELSE (CASE WHEN ListPrice = StandardCost THEN 1 WHEN NOT (ListPrice = StandardCost) THEN 0 ELSE NULL END) END)) = 1
3.group by中使用case when
linq代码： Products.GroupBy(P => P.StandardCost > 1000? P.Color : P.SellStartDate.ToString()) sql原型： -- Region Parameters DECLARE @p0 Decimal(8,4) = 1000 -- EndRegion SELECT [t1].[value] AS [Key] FROM ( SELECT (CASE WHEN [t0].[StandardCost] > @p0 THEN CONVERT(NVarChar(MAX),[t0].[Color]) ELSE CONVERT(NVarChar(MAX),[t0].[SellStartDate]) END) AS [value] FROM [SalesLT].[Product] AS [t0] ) AS [t1] GROUP BY [t1].[value] GO
4.group by sum
select new { TaskId = a.TaskID, TaskName = a.TaskName, ProjectId = a.ProjectID, ProjectName = d.ProjectName, QuestionId = b.QuestionID, QuestionState = b.QuestState, HouseId = c.BFID }) group a by new { a.TaskId,a.TaskName,a.ProjectId,a.ProjectName } into tq select new { tq.Key, QuestionCnt = tq.Count(), QuestionSortOut = tq.Sum(m=>!new string[] {"0","1"}.Contains(m.QuestionState)? 1 : 0) }
5.group by avg
var categories = from p in db.Products group p by p.CategoryID into g select new { g.Key, ExpensiveProducts = from p2 in g where p2.UnitPrice > g.Average(p3 => p3.UnitPrice) select p2 };
18. Aggregate
说明：根据输入的表达式获取聚合值；不延迟。即是说：用一个种子值与当前元素通过指定的函数来进行对比来遍历集合中的元素，符合条件的元素保留下来。如果没有指定种子值的话，种子值默认为集合的第一个元素。
| Where | 过滤；延迟 |
| Select | 选择；延迟 |
| Distinct | 查询不重复的结果集；延迟 |
| Count | 返回集合中的元素个数，返回INT类型；不延迟 |
| LongCount | 返回集合中的元素个数，返回LONG类型；不延迟 |
| Sum | 返回集合中数值类型元素之和，集合应为INT类型集合；不延迟 |
| Min | 返回集合中元素的最小值；不延迟 |
| Max | 返回集合中元素的最大值；不延迟 |
| Average | 返回集合中的数值类型元素的平均值。集合应为数字类型集合，其返回值类型为double；不延迟 |
| Aggregate | 根据输入的表达式获取聚合值；不延迟 |
