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	根据输入的表达式获取聚合值；不延迟