LINQ to SQL statement (2) Count/sum/min/max/avg operator

Usage Scenarios

Similar to aggregate functions in SQL, for statistical data, without delay. such as the number of elements in the returned sequence, sum, minimum, maximum, averaging.

Count

Description: Returns the number of elements in the collection, returns an int type, and generates a SQL statement of select COUNT (*) from

1. Simple Form

Returns the order quantity.

var q = context. Orders.count ();

2. Form of condition

Returns the number of customers in London.

var " London ");

LongCount

Description: used to return the number of elements in the collection, return a long type, no delay, can be used to return the number of elements of the collection of comparisons, the visual case can be used LongCount to count the number of elements, return login type is more accurate. The generated SQL statement is select COUNT_BIG (*) from.

var q = context. Customers.longcount ();

Sum

Description: used to return the sum of elements of a collection of numeric types, and the elements in the collection should be of type int. No delay. Generate SQL statement for select SUM (...) From

1. Simple Form

Return the total cost of the order:

var q = context. Orders.select (n = n.freight). Sum ();

2. Mapping form

Returns the total number of items ordered:

var q = context. Products.sum (n = n.unitsonorder);

Min

Description: returns the minimum value of an element in the collection. No delay. Generated SQL statement Select MIN (...) From

1. Simple Form

Returns the element with the lowest commodity price:

var q = context. Orders.select (n = n.freight). Min ();

2. Mapping form

Returns the element with the lowest shipping cost in the order:

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

3. Combination form

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

var  from inch context. Products                        Group p by P.categoryid into T                        Selectnew                        {                            = T.key,                              from inch where Select p2                        };

Max

Description: Returns the maximum value of an element in the collection. No delay. The resulting SQL statement is SELECT MAX (*) from

1. Simple Form

Returns the element with the largest price of the commodity:

  var t = db. Products.select (n = n.unitprice). Max ();

2. Mapping form

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

3. Combination form

Find the element with the largest unit price in each category:

var categories = from in      db. Products    Group p by P.categoryid into G    Selectnew  {        G.key,        =             from inch g             where p2. UnitPrice = = G.max (P3 = p3. UnitPrice)            Select P2

Avg

Description: used to return the average of a numeric type in a collection, which should be a collection of numeric types. Returns a double type without delay. Generated SQL statement for select AVG (...) From

1. Simple Form

Returns the average value of the commodity price:

var t = db. Products.select (p = p.unitprice). Average ();

2. Mapping form

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

3. Combination form

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

var  from inch db. Products                        Group C by C.categoryid into G                        Selectnew                            {                                = G.key,                                  from inch where Select P                            };

LINQ to SQL statement (2) Count/sum/min/max/avg operator