LINQ-Query [LINQ learning-1]

ArticleDirectory

• Count/sum/MIN/max/AVG Operator

1. Common query:

 
VaR q = from CInDB. Customers where c. City ="London"Select C. contactname;
 

Note: Select must be placed behind

 
DB. MERs Mers is the data source
 

 
C is one of the customers arrays.
 

 
Where Condition Statement
 

 
Select returned content. For example, select C. contactname indicates that all qualified contactnames are returned.
 

2. SQL in-like queries

PublicIqueryable <company> getcompanysbyids (list <guid> IDs) {var query = from ETSInGetquery () Where IDs. Contains (ETS. companyid) Select ETS;ReturnQuery ;}
 

 
There is a big difference between LINQ and SQL. Use array contains for loop comparison
 

 
This record is selected if IDs contains the ETS. companyid value.
 

3. nested Query

 
Note: Each object in the returned object set contains a set of discountedproducts attributes. That is, each object is also a collection class.
 

VaR q = from oInDB. Orders selectNew{O. orderid, discountedproducts = from ODInO. orderdetails where OD. Discount> 0.0 select OD, freeshippingdiscount = O. Freight };

Statement Description: Use nested query to return the sequence of all orders and their orderid, The subsequence of the items in the discount order, and the amount saved for free delivery.
 

4. Distinct format:

Note: The filter fields have different values. Used to query non-repeated result sets. The SQL statement is: Select distinct [City] from [MERs]

 
VaR q = (from CInDB. Customers select C. City). Distinct ();
 

Statement Description: query the countries covered by customers.

Count/sum/MIN/max/AVG Operator

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

Aggregate

Description: Obtain the aggregate value based on the input expression without delay. That is to say, a seed value is used to compare with the current element through the specified function to traverse the elements in the set, and the elements that meet the conditions are retained. If no seed value is specified, the seed value is the first element of the set by default.

 
Conversion format:
 

 
After a query is returned, you can use query. tolist <> () and other methods to convert the format;
 

 
You can use. First <> () to obtain the first data.