linq学习笔记(5):Count/Sum/Min/Max/Avg

时间:2021-05-05 22:51:05

     Count/Sum/Min/Max/Avg用于统计数据,比如统计一些数据的个数,求和,最小值,最大值,平均数。

     1.Count:返回集合中的元素个数,返回INT类型;不延迟。生成SQL语句为:SELECT COUNT(*) FROM

描述:获得数据库中顾客的数量

语句:

            var q  =
                ndc.Customers.Count();

对应SQL语句:

SELECT   COUNT ( * AS   [ value ]
FROM   [ dbo ] . [ Customers ]   AS   [ t0 ]

描述:带条件的查询,获取数据库中为停产的产品

语句:

var q = db.Products.Count(p => !p.Discontinued);

对应SQL语句:

SELECT   COUNT ( * AS   [ value ]
FROM   [ dbo ] . [ Products ]   AS   [ t0 ]
WHERE   NOT  ( [ t0 ] . [ Discontinued ]   =   1 )

     2.LongCount

     说明:返回集合中的元素个数,返回LONG类型;不延迟。对于元素个数较多的集合可视情况可以选用LongCount来统计元素个数,它返回long类型,比较精确。生成SQL语句为:SELECT COUNT_BIG(*) FROM

描述:获得数据库中顾客的数量

语句:

            var q  =
                ndc.Customers.LongCount();

对应SQL语句:

SELECT  COUNT_BIG( * AS   [ value ]
FROM   [ dbo ] . [ Customers ]   AS   [ t0 ]

     3.Sum

说明:返回集合中数值类型元素之和,集合应为INT类型集合;不延迟。生成SQL语句为:SELECT SUM(…) FROM

1).简单形式

描述:获取所有订单的总运费

语句:

var q  =  db.Orders.Select(o  =>  o.Freight).Sum();

对应SQL:

 

SELECT   SUM ( [ t0 ] . [ Freight ] AS   [ value ]
FROM   [ dbo ] . [ Orders ]   AS   [ t0 ]

2).映射模式

描述:获取产品UnitsOnOrder的数量

语句:

var q  =  db.Products.Sum(p  =>  p.UnitsOnOrder);

对应SQL语句:

SELECT   SUM ( CONVERT ( Int , [ t0 ] . [ UnitsOnOrder ] ))  AS   [ value ]
FROM   [ dbo ] . [ Products ]   AS   [ t0 ]

     4.Min

说明:返回集合中元素的最小值;不延迟。生成SQL语句为:SELECT MIN(…) FROM

1).简单形式

描述:找到产品的最低价格

语句:

var q  =  db.Products.Select(p  =>  p.UnitPrice).Min();

对应SQL语句:

SELECT   MIN ( [ t0 ] . [ UnitPrice ] AS   [ value ]
FROM   [ dbo ] . [ Products ]   AS   [ t0 ]

2).映射形式

描述:从订单中查找最低运费

语句:

var q  =  db.Orders.Min(o  =>  o.Freight);

对应SQL:

SELECT   MIN ( [ t0 ] . [ Freight ] AS   [ value ]
FROM   [ dbo ] . [ Orders ]   AS   [ t0 ]

     5.Max

说明:返回集合中元素的最大值;不延迟。生成SQL语句为:SELECT MAX(…) FROM

1).简单形式

描述:使用Max找到最新雇佣的雇员入职日期

语句:

var q  =  db.Employees.Select(e  =>  e.HireDate).Max();

对应SQL:

SELECT   MAX ( [ t0 ] . [ HireDate ] AS   [ value ]
FROM   [ dbo ] . [ Employees ]   AS   [ t0 ]

2).映射形式

描述:用Max查找产品中最大的UnitsInStock

语句:

var q  =  db.Products.Max(p  =>  p.UnitsInStock);

对应SQL语句:

SELECT   MAX ( [ t0 ] . [ UnitsInStock ] AS   [ value ]
FROM   [ dbo ] . [ Products ]   AS   [ t0 ]

     6.Average

说明:返回集合中的数值类型元素的平均值。集合应为数字类型集合,其返回值类型为double;不延迟。生成SQL语句为:SELECT AVG(…) FROM

1).简单形式

描述:获取订单的平均运费

语句:

var q  =  db.Orders.Select(o  =>  o.Freight).Average();

对应SQL:

SELECT   AVG ( [ t0 ] . [ Freight ] AS   [ value ]
FROM   [ dbo ] . [ Orders ]   AS   [ t0 ]

2).映射形式

描述:获取产品的平均价格

语句:

 

var q  =  db.Products.Average(p  =>  p.UnitPrice);

对应SQL:

 

 

SELECT   AVG ( [ t0 ] . [ UnitPrice ] AS   [ value ]
FROM   [ dbo ] . [ Products ]   AS   [ t0 ]