Linq to SQL (Group By/Having/Count/Sum/Min/Max/Avg operator ),

Linq to SQL (Group By/Having/Count/Sum/Min/Max/Avg operator ),
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.

 

 

 

 

 

Applicable scenarios: Statistical data, such as the number, sum, minimum, maximum, and average of some data.

Count

Description: Returns the number of elements in the Set, INT type; no delay. The SQL statement is: SELECT COUNT (*) FROM

1. Simple Form:

Obtain the number of customers in the database:

var q = db.Customers.Count();

2. Conditional form:

Obtain the number of unsold products in the database:

var q = db.Products.Count(p => !p.Discontinued);

LongCount

Description: Returns the number of elements in the set. The return value belongs to the LONG type. No delay is required. LongCount can be used to count the number of elements in a set with a large number of elements. It returns the long type and is accurate. The SQL statement is: SELECT COUNT_BIG (*) FROM

var q = db.Customers.LongCount();

Sum

Description: Returns the sum of numeric elements in the set. The set must be an INT type set without delay. The SQL statement is: SELECT SUM (...) FROM

1. Simple Form:

Get the total freight for all orders:

var q = db.Orders.Select(o => o.Freight).Sum();

2. ing format:

Obtain the total number of orders for all products:

var q = db.Products.Sum(p => p.UnitsOnOrder);

Min

Description: Returns the minimum value of an element in a set without delay. The SQL statement is: SELECT MIN (...) FROM

1. Simple Form:

Find the lowest unit price for any product:

var q = db.Products.Select(p => p.UnitPrice).Min();

2. ing format:

Find the lowest freight for any order:

var q = db.Orders.Min(o => o.Freight);

3. elements:

Find the product with the lowest unit price in each category:

var categories =    from p in db.Products    group p by p.CategoryID into g    select new {        CategoryID = g.Key,        CheapestProducts =            from p2 in g            where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)            select p2    };

Max

Description: Returns the maximum number of elements in a set without delay. The SQL statement is: SELECT MAX (...) FROM

1. Simple Form:

Find the latest employment date of any employee:

var q = db.Employees.Select(e => e.HireDate).Max();

2. ing format:

Find the maximum inventory of any product:

var q = db.Products.Max(p => p.UnitsInStock);

3. elements:

Find the product with the highest unit price in each category:

var categories =    from p in db.Products    group p by p.CategoryID into g    select new {        g.Key,        MostExpensiveProducts =            from p2 in g            where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)            select p2    };

Average

Description: Returns the average value of the value type element in the set. The set should be a set of numeric types, and its return value type is double; no delay. The SQL statement is: SELECT AVG (...) FROM

1. Simple Form:

Get the average freight for all orders:

var q = db.Orders.Select(o => o.Freight).Average();

2. ing format:

Obtain the average unit price of all products:

var q = db.Products.Average(p => p.UnitPrice);

3. elements:

Find the product whose unit price is higher than the average unit price of the category:

var categories =    from p in db.Products    group p by p.CategoryID into g    select new {        g.Key,         ExpensiveProducts =            from p2 in g            where p2.UnitPrice > g.Average(p3 => p3.UnitPrice)            select p2    };