LINQ to SQL statement (2) Count/Sum/Min/Max/Avg operator,

LINQ to SQL statement (2) Count/Sum/Min/Max/Avg operator,
Use Cases

Similar to aggregate functions in SQL, this function is used for statistics without delay. For example, return the number of elements in the sequence, sum, minimum, maximum, and average.

Count

Note:Returns the number of elements in the Set, Int type, and the SQL statement is SELECT COUNT (*) FROM.

1. Simple Form

Return order quantity.

 var q = context.Orders.Count(); 

2. Condition form

Returns the number of customers in London.

var q = context.Customers.Count(p => p.City == "London");

LongCount

Note:Return the number of elements in the collection, return the Long type, no delay, can be used to return a set of comparing the number of elements, you can use LongCount to count the number of elements, the returned Login type is more accurate. The generated SQL statement is SELECT COUNT_BIG (*) FROM.

 var q = context.Customers.LongCount();

Sum

Note:Returns the sum of numeric elements in a set. The elements in the set must be INT. No delay. Generate an SQL statement as SELECT SUM (...) FROM

1. Simple Form

Return the total freight for the order:

 var q = context.Orders.Select(n => n.Freight).Sum();

2. ing form

Return the total number of orders for the item:

 var q = context.Products.Sum(n => n.UnitsOnOrder);

Min

Note:Returns the minimum value of an element in a set. No delay. Generated SQL statement SELECT MIN (...) FROM

1. Simple Form

Returns the lowest product price element:

 var q = context.Orders.Select(n => n.Freight).Min();

2. ing form

Elements of the lowest freight in the returned order:

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

3. combination form

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

 var q = from p in context.Products                        group p by p.CategoryID into t                        select new                        {                            CategoryID = t.Key,                            Cheapest = from p2 in t where p2.UnitPrice == t.Min(p3 => p3.UnitPrice) select p2                        };

Max

Returns the maximum value of an element in a set. No delay. The generated SQL statement is SELECT MAX (*) FROM

1. Simple Form

Returns the element with the largest unit price:

  var t = db.Products.Select(n => n.UnitPrice).Max();

2. ing 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 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

Avg

Note:Returns the average value of the value type in a set. The set must be a set of value types. Returns the Double type without delay. The generated SQL statement is SELECT AVG (...) FROM

1. Simple Form

Returns the average value of the product price:

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

2. ing form

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

3. combination form

Find the product whose unit price is higher than the average unit price of the category:

 var t = from c in db.Products                        group c by c.CategoryID into g                        select new                            {                                CategoryID = g.Key,                                Price = from p in g where p.UnitPrice >= g.Average(n => n.UnitPrice) select p                            };