LINQ experience (8) -- Union all/Union/intersect and top/bottom and paging and sqlmethods of the linq statement

We will continue to explain the LINQ statements. In this article, we will discuss Union all/Union/intersect operations, top/bottom operations, paging operations, and sqlmethods operations.

Union all/Union/intersect operation

Applicable scenarios: process two sets, such as append, merge, take the same item, intersection item, and so on.

Concat (connection)

Note: different sets are connected, and the same items are not automatically filtered; latency.

1. Simple Form:

VaRQ = (FromCInDB. MERsSelectC. Phone). Concat (FromCInDB. MERsSelectC. Fax). Concat (FromEInDB. EmployeesSelectE. homephone );
 

Statement Description: return the telephone and fax of all consumers and employees.

2. Compound form:

 
VaRQ = (FromCInDB. MERsSelect New{Name = C. companyName, C. PHONE}). Concat (FromEInDB. EmployeesSelect New{Name = E. firstname +""+ E. lastname, phone = E. homephone });
 

Statement Description: returns the name and phone number of all consumers and employees.

Union)

Note: different sets are connected to automatically filter the same items; latency. Merge the two sets to filter the same items.

 
VaRQ = (FromCInDB. MERsSelectC. Country). Union (FromEInDB. EmployeesSelectE. Country );
 

Statement Description: query the country in which the customer and employee are located.

Intersect (intersection)

Description: obtains the intersection items and delays. That is, the same item (intersection) of different sets is obtained ). That is, first traverse the first set, find all unique elements, then traverse the second set, and compare each element with the elements found above, returns all elements that appear in both sets.

 
VaRQ = (FromCInDB. MERsSelectC. Country). Intersect (FromEInDB. EmployeesSelectE. Country );
 

Statement Description: query the countries in which customers and employees are located.

Except (and non)

Note: intersection items are excluded; delay. That is, to delete the same items from a collection and from another collection. First, traverse the first set, find all unique elements, and then traverse the second set. Then, return all elements in the second set that are not present in the previous element set.

 
VaRQ = (FromCInDB. MERsSelectC. Country). Country T (FromEInDB. EmployeesSelectE. Country );
 

Statement Description: Query countries in which customers and employees are located.

Top/bottom operations

Applicable scenarios: retrieve the desired data in a proper amount, not all of which enhance the performance.

Take

Description: gets the first n elements of the Set; delay. That is, only a limited number of result sets are returned.

 
VaRQ = (FromEInDB. EmployeesOrderbyE. hiredateSelectE). Take (5 );
 

Statement Description: query the first five employees.

Skip

Note: skip the first n elements of the Set; delay. That is, we will skip the given number and return the result set.

VaRQ = (FromPInDB. ProductsOrderbyP. unitpriceDescendingSelectP). Skip (10 );
 

Statement Description: query the 10 most expensive products.

Takewhile

Note: The acquisition is stopped until a condition is set up. That is, the conditions are used to determine the elements in the source sequence in sequence, and the elements that meet the judgment conditions are returned. The judgment operation ends at the return of false or the end of the source sequence.

Skipwhile

Note: Skip is stopped until a condition is set up. delay. That is, the conditions are used to determine the elements in the source sequence and skip the first element that meets the judgment condition. Once the judgment returns false, no judgment is made and all the remaining elements are returned.

Paging

Use Cases: Use skip and take to perform data paging.

 
VaRQ = (FromCInDB. MERsOrderbyC. contactnameSelectC). Skip (50). Take (10 );
 

Statement Description: skip the first 50 records and retrieve the next 10 records. The product table is displayed on page 6th.

VaRQ = (FromPInDB. ProductsWhereP. productid> 50OrderbyP. productidSelectP). Take (10 );
 

Sqlmethods operations

In the LINQ to SQL statement, sqlmethods operations are provided for us and further convenience is provided. For example, the like method is used to customize the configuration expression, and equals is used to compare whether it is equal.

Like

A custom wildcard expression. % Represents a string of zero or any length; _ represents a character; [] represents a character in a range; [^] represents a character not in a range. For example, query the consumer whose ID starts with "C.

VaRQ =FromCInDB. MERsWhere Sqlmethods. Like (C. customerid,"C %")SelectC;
 

For example, query the consumer IDNo"Axoxt" consumers:

VaRQ =FromCInDB. MERsWhere!Sqlmethods. Like (C. customerid,"A_o_t")SelectC;
 

Datediffday

Description: Compares two variables. The options include datediffday, datediffhour, datediffmillisecond, datediffminute, datediffmonth, datediffsecond, and datediffyear.

VaRQ =FromOInDB. OrdersWhere Sqlmethods. Datediffday (O. orderdate, O. shippeddate) <10SelectO;
 

Statement Description: queries all orders within 10 days.

Compiled query (query and editing) Operations

Note: We didn't have a good way to edit and re-query the SQL statement. Now we can do this. See the following example:

 // 1. Create compiled Query  Northwinddatacontext DB =New   Northwinddatacontext (); VaR Fn = compiledquery. Compile (( Northwinddatacontext DB2, String City) => From C In Db2.mers MERs Where C. City = City Select C ); // 2. query the Consumers whose city is London, expressed by the loncusts set. In this case, you can bind the data binding control.  VaR Loncusts = FN (dB ," London "); // 3. query the Consumers whose city is Seattle  VaR Seacusts = FN (dB ," Seattle ");

Next, we will continue with the introduction. Stay tuned from: Li yongjing