LINQ experience (7) -- group by/having and exists/In/any/all/contains of the linq to SQL statement

I read the LINQ written by Li yongjing. I tried it once. Now, only the tested ones are recorded.
Group by/having Operator
VaR q =
From P in db. Products
Group P by P. categoryid into G
Select g;
Some people will say having. Actually, I don't know how to use it. Failed after several attempts.

Maximum Value

Note: first, sort by categoryid to determine the products with the largest unit price in each product category. Take out the categoryid value and assign the unitprice value to maxprice.

var q =from p in db.Productsgroup p by p.CategoryID into gselect new {g.Key,MaxPrice = g.Max(p => p.UnitPrice)};

Min, average, and sum are different only in count. It needs to be written in this form
VaR q =
From P in db. Products
Group P by P. categoryid into G
Select New {
G. Key,
Numproducts = G. Count ()
};
8. Conditional count

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.

var q =from p in db.Productsgroup p by p.CategoryID into gselect new {g.Key,NumProducts = g.Count(p => p.Discontinued)};

In fact, this function is not very good. This is mainly because count () can only contain boolean data.

9. Where restrictions

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

VaR q = from P in dB. productsgroup P by P. categoryid into gwhere G. count ()> = 10 // it seems that only count is allowed here. if I max (P. ID. 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.

This function is also quite bad. Very limited.

10. Multiple Columns

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.

var categories =from p in db.Productsgroup p by new{p.CategoryID,p.SupplierID}into gselect new{g.Key,g};

11. Expression)

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.

var categories =from p in db.Productsgroup p by new { Criterion = p.UnitPrice > 10 } into gselect g;

Any
Note: It is used to determine whether an element in a set meets a certain condition without delay. (If the condition is null, true is returned if the set is not null. Otherwise, false is returned ). There are two forms: simple form and conditional form.
1. Simple Form:
var q =from c in db.Customerswhere !c.Orders.Any()select c;
The 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. Conditional form:
VaR q = from C in db. categorieswhere C. Products. Any (P => P. discontinued) // note that discontinued is a Boolean select C;
The 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]))
 
Contains multiple values:
string[] cities =new string[] { "Seattle", "London", "Vancouver", "Paris" };var q = db.Customers.Where(p=>cities.Contains(p.City)).ToList();