LINQ↔Lambda↔SQL 互相“翻译”

一个星期又过去了，又该总结一些零散的知识点。

这次是关于SQL、Lambda、LINQ查询的一些知识，我使用LINQPad来做试验的，关于LINQPad怎么使用就不说了，下载下来自己摸索一下就会了

准备两张表

表名分别是：ATE_ItemData和StationProcessCode，至于数据类型嘛，这个自己随便定义吧。

1、首先是查询全部数据

SQL:
    select * from ATE_ItemData
Lambda:
    ATE_ItemData.Select(a=>a)
LINQ:
    from a in ATE_ItemData select a

2、查询部分数据

SQL:
    select * from ATE_ItemData AS A
    inner join StationProcessCode AS S
    on A.ProcessCode=S.ProcessCode
Lambda:
    ATE_ItemData.Join(StationProcessCode,
    a=>a.ProcessCode,b=>b.ProcessCode,(a,b)=>new
    {a.ID,a.PDO,b.TP1_Voltage,b.TP2_Voltage}
LINQ:
    from a in ATE_ItemData
    join b in StationProcessCode equals b.ProcessCode
    select new{a.ID,a.PDO,b.TP1_Voltage,b.TP2_Voltage}

3、根据条件查询

SQL:
    select ID,SN,PDO from where ID=1
Lambda:
    ATE_ItemData.Where(a=>a.ID==1).Select(c=>new{c.ID,c.SN,c.PDO})
LINQ:
    from a in ATE_ItemData where a.ID==1 select new{a.ID,a.SN,a.PDO}

4、多表联合查询(我只用两张表查询)

SQL:
    select * from ATE_ItemData AS A
    inner join StationProcessCode AS S
    ON A.ProcessCode=S.ProcessCode
Lambda:
    ATE_ItemData.Join(StationProcessCode, a=>a.ProcessCode,
    b=>b.ProcessCode, (a,b)=> new     
    {a.ID,a.PDO,a.SN,b.TP1_Voltagge,TP2_Voltage})
LINQ:
    from a in ATE_ItemData
    join b in StationProcessCodes
    on a.ProcessCode equals b.ProcessCode
    select new {a.ID,a.PDO,a.SN,b.TP1_Voltage,TP2_Voltage}

 5、排序查询

　　5.1、正序排序

SQL:
    select * from ATE_ItemData order by ID
Lambda:
    ATE_ItemData.OrderBy(c=>c.ID)
LINQ:
    from a in ATE_ItemData orderby a.ID select a

　　5.2、倒序排序

SQL:
    select * from ATE_ItemData order by ID desc
Lambda:
    ATE_ItemData.OrderByDescending(c=>c.ID)
LINQ:
    from a in ATE_ItemData
    orderby a.ID descending
    select a

6、分组查询

SQL:
    分组求和无条件查询
    select count(*) from ATE_ItemData group by PDO
    分组求和有条件查询
    select count(*) from ATE_ItemData group by PDO having count(*)>8
Lambda:
    分组求和无条件查询
    ATE_ItemData.GroupBy(a=>a.PDO)。Select(b=>new{Num=b.Count()})
    分组求和有条件查询
    ATE_ItemData.GroupBy(a=>a.PDO).Where(b=>(b.Count()>8)).Select(b=>new{Num=b.Count()})
LINQ:
    分组求和无条件查询
    from a in ATE_ItemData
    group by a.PDO into b
    select new{Num=b.Count()}
    分组求和有条件查询
    from a in ATE_ItemData
    group by a.PDO into b
    where b.Count()>8
    select new{Num=b.Count()}