LINQ↔Lambda↔SQL 互相“翻译”
一个星期又过去了,又该总结一些零散的知识点。
这次是关于SQL、Lambda、LINQ查询的一些知识,我使用LINQPad来做试验的,关于LINQPad怎么使用就不说了,下载下来自己摸索一下就会了
准备两张表
表名分别是:ATE_ItemData和StationProcessCode,至于数据类型嘛,这个自己随便定义吧。
1、首先是查询全部数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SQL: 2 select * from ATE_ItemData 3 Lambda: 4 ATE_ItemData.Select(a=>a) 5 LINQ: 6 from a in ATE_ItemData select a
2、查询部分数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SQL: 2 select * from ATE_ItemData AS A 3 inner join StationProcessCode AS S 4 on A.ProcessCode=S.ProcessCode 5 Lambda: 6 ATE_ItemData.Join(StationProcessCode, 7 a=>a.ProcessCode,b=>b.ProcessCode,(a,b)=>new 8 {a.ID,a.PDO,b.TP1_Voltage,b.TP2_Voltage} 9 LINQ: 10 from a in ATE_ItemData 11 join b in StationProcessCode equals b.ProcessCode 12 select new{a.ID,a.PDO,b.TP1_Voltage,b.TP2_Voltage}
3、根据条件查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SQL: 2 select ID,SN,PDO from where ID=1 3 Lambda: 4 ATE_ItemData.Where(a=>a.ID==1).Select(c=>new{c.ID,c.SN,c.PDO}) 5 LINQ: 6 from a in ATE_ItemData where a.ID==1 select new{a.ID,a.SN,a.PDO}
4、多表联合查询(我只用两张表查询)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SQL: 2 select * from ATE_ItemData AS A 3 inner join StationProcessCode AS S 4 ON A.ProcessCode=S.ProcessCode 5 Lambda: 6 ATE_ItemData.Join(StationProcessCode, a=>a.ProcessCode, 7 b=>b.ProcessCode, (a,b)=> new 8 {a.ID,a.PDO,a.SN,b.TP1_Voltagge,TP2_Voltage}) 9 LINQ: 10 from a in ATE_ItemData 11 join b in StationProcessCodes 12 on a.ProcessCode equals b.ProcessCode 13 select new {a.ID,a.PDO,a.SN,b.TP1_Voltage,TP2_Voltage}
5、排序查询
5.1、正序排序
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SQL: 2 select * from ATE_ItemData order by ID 3 Lambda: 4 ATE_ItemData.OrderBy(c=>c.ID) 5 LINQ: 6 from a in ATE_ItemData orderby a.ID select a
5.2、倒序排序
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SQL: 2 select * from ATE_ItemData order by ID desc 3 Lambda: 4 ATE_ItemData.OrderByDescending(c=>c.ID) 5 LINQ: 6 from a in ATE_ItemData 7 orderby a.ID descending 8 select a
6、分组查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SQL: 2 分组求和无条件查询 3 select count(*) from ATE_ItemData group by PDO 4 分组求和有条件查询 5 select count(*) from ATE_ItemData group by PDO having count(*)>8 6 Lambda: 7 分组求和无条件查询 8 ATE_ItemData.GroupBy(a=>a.PDO)。Select(b=>new{Num=b.Count()}) 9 分组求和有条件查询 10 ATE_ItemData.GroupBy(a=>a.PDO).Where(b=>(b.Count()>8)).Select(b=>new{Num=b.Count()}) 11 LINQ: 12 分组求和无条件查询 13 from a in ATE_ItemData 14 group by a.PDO into b 15 select new{Num=b.Count()} 16 分组求和有条件查询 17 from a in ATE_ItemData 18 group by a.PDO into b 19 where b.Count()>8 20 select new{Num=b.Count()}
posted on 2016-01-24 11:31 LiGengMing 阅读(442) 评论(0) 编辑 收藏 举报