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

Document directory

• Group by/having Operator
• Exists/In/any/all/contains operator
• Any
• All
• Contains

We will continue to explain the LINQ to SQL statements. In this article, we will discuss the Group by/having operators and the exists/In/any/all/contains operators.

Group by/having Operator

Applicable scenarios: Grouping data. narrow down the scope of data for us.

Description: Assigns and returns the enumerated objects after grouping the input parameters. Group; 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 products by categoryid.

Note: from P in db. Products indicates that the product object is retrieved from the table. Group P by P. categoryid into G indicates that P is classified by the categoryid field. The result is named G. Once it is renamed again, the scope of P ends. Therefore, only select g can be selected. Of course, you do not need to rename it. You can write it like this:

var q =    from p in db.Products    group p by p.CategoryID;

We use the following representation:

If you want to traverse all records in a category, the following code is displayed:

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 into g    select new { CategoryID = g.Key, g }; 

Note: In this LINQ statement, there are two properties: categoryid and G. The essence of this anonymous class is to repackage the returned result set. Encapsulate g property into a complete group. As shown in:

To traverse all records in an anonymous class, do the following:

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 maximum unit price of each categoryid.

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.

4. Minimum value

var q =    from p in db.Products    group p by 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 for each categoryid.

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

5. Average Value

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 obtain the average unit price of each categoryid.

Note: category by categoryid is used to retrieve the value of categoryid and the average unit price of each category product.

6. Sum

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 total unit price of each categoryid.

Note: category by categoryid is used to retrieve the value of categoryid and the total unit price of each product category.

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

8. 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 products that are out of stock 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.

9. 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.

10. 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: 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.

11. Expression)

var categories =    from p in db.Products    group p by new { Criterion = p.UnitPrice > 10 } 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 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.

Exists/In/any/all/contains operator

Applicable scenarios: Used to determine elements in a set and further narrow down the scope.

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:

Only customers without orders are returned:

var q =    from c in db.Customers    where !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:

Only the following types of out-of-stock products are returned:

var q =    from c in db.Categories    where c.Products.Any(p => p.Discontinued)    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])    )

All

Determines whether all elements in a set meet a certain condition without delay.

1. Conditional form

var q =    from c in db.Customers    where c.Orders.All(o => o.ShipCity == c.City)    select c;

Statement Description: In this example, all orders are sent to customers in the city where they are located or customers who have not placed orders.

Contains

Note: It is used to determine whether a collection contains a certain element without delay. It connects 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: searches for orders of the "arout", "bolid", and "fissa" customers. First, an array is defined and linq ins is used in LINQ to SQL. The array contains all the mermerids, that is, all the mermerids in the returned results are in this set. That is, in. You can also put the definition of an array in a LINQ to SQL statement. For example:

var q = (    from o in db.Orders    where (    new string[] { "AROUT", "BOLID", "FISSA" })    .Contains(o.CustomerID)    select o).ToList();

Not contains:

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 the customer who contains an order with orderid 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 cities of Seattle, London, Paris, or Vancouver.

To sum up, we illustrate the following statements:

Group by/having
Group data; Delay

Any
Used to determine whether an element in a set meets a certain condition without delay.

All
Used to determine whether all elements in a set meet a certain condition without delay

Contains
Used to determine whether a collection contains a certain element without delay.