[Turn]linq to SQL (Group by/having/count/sum/min/max/avg operator)

This article transferred from: http://www.cnblogs.com/jack-liang/archive/2011/03/22/1991554.html

Group by/having operator

Scenario : grouping data to narrow the scope for us to find data.

Description : An enumerable object that is assigned and returned after a group operation on an incoming parameter. To group or delay

1. Simple form:

var q = from    p in db. Products    Group p by P.categoryid into G    select G;

Statement Description: Use GROUP by to divide the product by CategoryID.

Description: from P in db. Products means that the product objects are taken out of the table. Group p by P.categoryid into G indicates that P is categorized by the CategoryID field. The result is named G, and once renamed, the scope of P is ended, so the last select can only select G. Of course, you do not have to rename it to write:

var q = from    p in db. Products    Group p by P.categoryid;

We use the expression:

If you want to traverse all the records in a category, this:

foreach (Var gp in Q) {    if (GP. Key = = 2)    {        foreach (var item in GP)        {            //do something        }    }}

2.Select Anonymous class:

var q = from    p in db. Products    Group p by P.categoryid to G    

Description: In this LINQ statement, there are 2 Property:categoryid and G. The essence of this anonymous class is to re-wrap the returned result set. Encapsulates the property of G into a complete grouping. As shown in the following:

If you want to traverse all the records in an anonymous class, do this:

foreach (Var gp in Q) {    if (GP. CategoryID = = 2)    {        foreach (var item in GP.G)        {            //do something        }    }}

3. Maximum Value

var q = from    p in db. Products    Group p by P.categoryid into G    Select new {        G.key,        maxprice = G.max (p = p.unitprice) 
   };

Statement Description: Use GROUP BY and Max to find the highest unit price per CategoryID.

Description: First according to CategoryID classification, judging the price of the largest products in each category. Remove the CategoryID value and assign the UnitPrice value to Maxprice.

4. Minimum value

var q = from    p in db. Products    Group P is p.categoryid into G    Select new {        G.key,        minprice = g.min (p = p.unitprice)    };

Statement Description: Use GROUP BY and Min to find the lowest unit price per CategoryID.

Description: First, according to CategoryID classification, judging the lowest unit price of products in each category. Remove the CategoryID value and assign the UnitPrice value to Minprice.

5. Average

var q = from    p in db. Products    Group p by P.categoryid into G    Select new {        G.key,        averageprice = g.average (p = p.unitprice )    };

Statement Description: Use GROUP by and average to get the average unit price per CategoryID.

Description: First by CategoryID classification, remove the CategoryID value and the average price of each product category.

6. Summation

var q = from    p in db. Products    Group p by P.categoryid into G    Select new {        G.key,        totalprice = g.sum (p = p.unitprice) 
   };

Statement Description: Use GROUP BY and sum to get the unit price totals for each CategoryID.

Description: First by CategoryID classification, take out the CategoryID value and the sum of the unit price in each classified product.

7. Counting

var q = from    p in db. Products    Group P is p.categoryid into G    Select new {        G.key,        numproducts = G.count ()    };

Statement Description: Use GROUP BY and count to get the number of products in each CategoryID.

Description: First by CategoryID classification, remove the CategoryID value and the number of individual products.

8. With conditional count

var q = from    p in db. Products    Group p by P.categoryid into G    Select new {        G.key,        numproducts = g.count (p = p.discontinued )    };

Statement Description: Use GROUP BY and count to get the number of discontinued products per CategoryID.

Description: First by CategoryID classification, remove the CategoryID value and the number of broken goods of each category. In the Count function, a lambda expression is used, and p in the lambda expression represents an element or object in the group, which is a product.

9.Where limit

var q = from    p in db. Products    Group P is p.categoryid into G    where G.count () >=    select new {        G.key,        productcount = g. Count ()    };

Statement Description: Based on the product's ―id grouping, the product number is queried for more than 10 of the ID and product quantity. This example uses the WHERE clause after the GROUP BY clause to find all categories with at least 10 products.

Description: A Where condition is nested at the outermost layer when translated into an SQL statement.

10. Multi-column (multiple Columns)

var categories = from    p in db. Products    Group p by new    {        P.categoryid,        P.supplierid    } to        g        Select New            {                G.key,                g            };

Statement Description: Use GROUP by to group products by CategoryID and SupplierID.

Description: Both by product classification, and by supplier classification. After by, new comes out an anonymous class. Here, key is essentially a class object, and key contains two Property:categoryid, SupplierID. With G. Key.categoryid can traverse the value of CategoryID.

11. Expressions (expression)

var categories = from    p in db. Products    Group p by new {Criterion = P.unitprice > ten} into G    select G;

Statement Description: Use GROUP by to return two product sequences. The first sequence contains products with a unit price greater than 10. The second sequence contains products with a unit price less than or equal to 10.

Description: By product unit Price is greater than 10 classification. The results are divided into two categories, greater than the one, less than and equal to another class.

Exists/in/any/all/contains operator

applicable scenario : Used to determine the elements in the set, further narrowing the scope.

Any

Description: Used to determine whether an element in the collection satisfies a condition; (If the condition is NULL, the collection returns true if it is not empty, otherwise false). There are 2 forms, namely the simple form and the conditional form.

1. Simple form:

Only customers who do not have an order are returned:

var q = from    C in db. Customers    where!c.orders.any ()    select C;

The generated SQL statement is:

SELECT [T0]. [CustomerID], [t0]. [CompanyName], [t0]. [Contactname],[t0]. [ContactTitle], [t0]. [Address], [t0]. [City], [t0]. [Region],[t0]. [PostalCode], [t0]. [Country], [t0]. [Phone], [t0]. [Fax] from [dbo]. [Customers] As [T0]where not (EXISTS (    SELECT NULL as [EMPTY] from [dbo].[ Orders] as [T1]    WHERE [t1].[ CustomerID] = [T0]. [CustomerID])   )

2. With conditional form:

Return only the categories that have at least one product that is out of stock:

var q = from    C in db. Categories    where c.products.any (p = p.discontinued)    select C;

The generated SQL statement is:

SELECT [T0]. [CategoryID], [t0]. [CategoryName], [t0]. [Description],[t0]. [Picture] from [dbo]. [Categories] As [T0]where EXISTS (    SELECT NULL as [EMPTY] from [dbo].[ Products] as [T1]    WHERE ([t1].[ Discontinued] = 1) and     ([t1].[ CategoryID] = [T0]. [CategoryID])    )

All

Description: Used to determine whether all elements in the set satisfy a certain condition;

1. With conditional form

var q = from    C in db. Customers    where c.orders.all (o = o.shipcity = = c.city)    select C;

Statement Description: This example returns all orders shipped to customers in their city or customers who have not placed an order.

Contains

Description: Used to determine whether an element is contained in a collection; It is a connection operation to two sequences.

string[] Customerid_set =    new string[] {"Arout", "Bolid", "Fissa"};var q = (from    o in db.) Orders    where Customerid_set.contains (o.customerid)    select O). ToList ();

Statement Description: Find orders for the three customers "Arout", "Bolid" and "Fissa". An array is defined, using contains in LINQ to SQL, and all the CustomerID are included in the array, that is, all CustomerID are within the set. That is in. You can also place the definition of an array in a LINQ to SQL statement. Like what:

var q = (from    o in db. Orders    WHERE (    new string[] {"Arout", "Bolid", "Fissa"})    . Contains (o.customerid)    select O). ToList ();

The not contains is reversed:

var q = (from    o in db. Orders    where! (    New string[] {"Arout", "Bolid", "Fissa"})    . Contains (o.customerid)    select O). ToList ();

1. Contains an object:

var order = (from O in db. Orders             where O.orderid = = 10248             select o). First (); var q = db. Customers.where (P = p.orders.contains (order)). ToList (); foreach (Var cust in q) {    foreach (var ord in Cust. Orders)    {        //do something    }}

Statement Description: This example uses contain to find which customer contains an order with a OrderID of 10248.

2. Contains multiple values:

string[] Cities =     New string[] {"Seattle", "London", "Vancouver", "Paris"};var q = db. Customers.where (p=>cities. Contains (p.city)). ToList ();

Statement Description: This example uses contains to find customers in the City of Seattle, London, Paris or Vancouver.

To summarize this, we have explained the following statement:

Group by/having	Group data; delay
Any	Used to determine whether an element in a set satisfies a condition;
All	Used to determine whether all elements in the collection meet a certain condition;
Contains	Used to determine whether an element is contained in a collection;

applicable Scenarios : statistics, such as the number of statistical data, sum, Minimum, maximum, average.

Count

Description : Returns the number of elements in the collection, returns an int type, and no delay. The generated SQL statement is: SELECT COUNT (*) from

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 to 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 to 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 to G    Select new {        G.key,         expensiveproducts = from            p2 in G            wher e P2. UnitPrice > g.average (p3 = p3. UnitPrice)            select P2    };

[Turn]linq to SQL (Group by/having/count/sum/min/max/avg operator)