Count/sum/min/max/avg of LINQ to SQL statements (3)

1. Simple form:

Get the number of customers in the database:

var q = db. Customers.count ();

2. With conditional form:

Get the number of non-broken products in the database:

var q = db. Products.count (p =!p.discontinued);

LongCount

Description: Returns the number of elements in the collection, returning a long type; For a collection with a large number of elements, the longcount can be used to count the number of elements, which returns a long type and is more accurate. The generated SQL statement is: SELECT COUNT_BIG (*) from

var q = db. Customers.longcount ();

Sum

Description: Returns the sum of the numeric type elements in the collection, which should be of type int, without delay. The generated SQL statement is: SELECT SUM (...) From

1. Simple form:

Get total shipping for all orders:

var q = db. Orders.select (o = o.freight). Sum ();

2. Mapping form:

Get the total number of orders for all products:

var q = db. Products.sum (p = p.unitsonorder);

Min

Description: Returns the minimum value of an element in the collection; The generated SQL statement is: SELECT MIN (...) From

1. Simple form:

Find the lowest unit price for any product:

var q = db. Products.select (p = p.unitprice). Min ();

2. Mapping form:

Find the lowest shipping cost for any order:

var q = db. Orders.min (o = o.freight);

3. Elements:

Find the product with the lowest unit price in each category:

var categories =

from P in Db. Products

Group p by P.categoryid into G

Select New {

CategoryID = G.key,

Cheapestproducts =

From P2 in G

where P2. UnitPrice = = G.min (P3 = p3. UnitPrice)

Select P2

};

Max

Description: Returns the maximum value of an element in the collection; The generated SQL statement is: SELECT MAX (...) From

1. Simple form:

Find the most recent hire date for any employee:

var q = db. Employees.select (e = e.hiredate). Max ();

2. Mapping form:

Find the maximum amount of inventory for any product:

var q = db. Products.max (p = p.unitsinstock);

3. Elements:

Find the product with the highest unit price in each category:

var categories =

from P in Db. Products

Group p by P.categoryid into G

Select New {

G.key,

Mostexpensiveproducts =

From P2 in G

where P2. UnitPrice = = G.max (P3 = p3. UnitPrice)

Select P2

};

Average

Description: Returns the average of a numeric type element in a collection. The collection should be a collection of numeric types whose return value type is double; The generated SQL statement is: SELECT AVG (...) From

1. Simple form:

Get the average shipping cost for all orders:

var q = db. Orders.select (o = o.freight). Average ();

2. Mapping form:

Get the average unit price for all products:

var q = db. Products.average (p = p.unitprice);

3. Elements:

Find products in each category that have a unit price higher than the average of that category:

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

};

Aggregate

Description: Gets the aggregated value according to the input expression, without delay. That is, a seed value is compared with the current element through the specified function to iterate through the elements in the collection, and the eligible elements remain. If you do not specify a seed value, the seed value defaults to the first element of the collection.