Group by/having for LINQ to SQL statements (6)

Tag: Height foreach class ice average src object equals group by

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 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 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 in  q) {    if2)    {        foreach (var in GP)        {            //dosomething        }     }}

2.Select Anonymous class:

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

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 in  q) {    if2)    {        foreach (var    in gp.g)        {            //dosomething        }     }}

3. Maximum Value

var q = from in      db. Products    Group P is p.categoryid into G    Selectnew  {        G.key,        = 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 in      db. Products    Group P is p.categoryid into G    Selectnew  {        G.key,        = 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 in      db. Products    Group P is p.categoryid into G    Selectnew  {        G.key,        = 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 in      db. Products    Group P is p.categoryid into G    Selectnew  {        G.key,        = 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 in      db. Products    Group p by P.categoryid into G    Selectnew  {        G.key,        = 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 in      db. Products    Group P is p.categoryid into G    Selectnew  {        G.key,        = 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 in      db. Products    Group p by P.categoryid into G    where    Selectnew {        G.key,        = 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 in      db. Products    new    {        P.categoryid,        P.supplierid    } to        g          SelectNew            {                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 in      db. Products    newten  } 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.

Group by/having for LINQ to SQL statements (6)