Group By/Having and Exists/

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;

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)        {            //do something        }    }}

3. 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.Products    group p by p.CategoryID into g    select new {        g.Key,        MaxPrice = g.Max(p => p.UnitPrice)    };

4. Minimum value

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.

var q =    from p in db.Products    group p by p.CategoryID into g    select new {        g.Key,        MinPrice = g.Min(p => p.UnitPrice)    };

5. Average Value

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

var q =    from p in db.Products    group p by p.CategoryID into g    select new {        g.Key,        AveragePrice = g.Average(p => p.UnitPrice)    };

6. Sum

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

var q =    from p in db.Products    group p by p.CategoryID into g    select new {        g.Key,        TotalPrice = g.Sum(p => p.UnitPrice)    };

7. Count

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

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.Products    group p by p.CategoryID into g    select new {        g.Key,        NumProducts = g.Count(p => p.Discontinued)    };

9. Where restrictions

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

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.

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.Products    group p by new { p.CategoryID, p.SupplierID } into g    select 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.Products    group p by new { Criterion = p.UnitPrice > 10 } into g    select g;

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:

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]. [MERs] 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.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;

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

2. contains multiple values:

string[] cities = new string[] { "Seattle", "London", "Vancouver", "Paris" };var q = db.Customers.Where(p=>cities.Contains(p.City)).ToList();

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.

 

 

See: http://www.pccode.net/info/2010/03/19/20100319-2569.html