Use group by 2

We often encounter the problem of using group by using LINQ. Here we will introduce how to solve the problem of using group by using LINQ.

1. Count

 
 

  
  
var q =  
  
  
from p in db.Products  
  
  
group p by p.CategoryID into g  
  
  
select new {  
  
  
g.Key,  
  
  
NumProducts = g.Count()  
  
  
}; 
 
 

Statement Description: Use group by and count to obtain the number of products in each categoryid.

Note: category by categoryid is used to retrieve the categoryid value and the quantity of products under each category.

2. 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 obtain the number of out-of-Stock Products in each categoryid.

Note: first, sort by categoryid to get the categoryid value and the number of out-of-stock items of each category. In the count function, lambda expressions are used. P in lambda expressions represents an element or object in the group, that is, a product.

3. Where restrictions

 
 

  
  
var q =  
  
  
from p in db.Products  
  
  
group p by p.CategoryID into g  
  
  
where g.Count() >= 10  
  
  
select new {  
  
  
g.Key,  
  
  
ProductCount = g.Count()  
  
  
}; 
 
 

Statement Description: queries IDs and product quantities with the product quantity greater than 10 based on the product ID group. In this example, after the group by clause, use the WHERE clause to find all categories of at least 10 products.

Note: when translated into SQL statements, the where condition is nested in the outermost layer.

4. Multiple Columns)

 
 

  
  
var categories =  
  
  
from p in db.Products  
  
  
group p by new  
  
  
{  
  
  
p.CategoryID,  
  
  
p.SupplierID  
  
  
}  
  
  
into g  
  
  
select new  
  
  
{  
  
  
g.Key,  
  
  
g  
  
  
}; 
 
 

Statement Description: LINQ uses group by to group products by categoryid and supplierid.

Note: products and suppliers are classified. After by, an anonymous class is created. Here, the key is actually a class object. The key contains two properties: categoryid and supplierid. You can use g. Key. categoryid to traverse the value of categoryid.

5. Expression)

 
 

  
  
var categories =  
  
  
from p in db.Products  
  
  
group p by new { Criterion = p.UnitPrice > 10 } into g  
  
  
select g; 
 
 

Statement Description: uses 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 of less than or equal to 10.

Note: whether the unit price is more than 10 categories. The result is divided into two types: greater than one type, smaller than or equal to the other type.