C # Union all/Union/intersect and top/bottom and paging and sqlmethods in LINQ

We will continue to explain the LINQ to SQL statements. In this article, we will discuss the 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:var q = (
　　　　 from c in db.Customers
　　　　 select c.Phone
　　　　).Concat(
　　　　 from c in db.Customers
　　　　 select c.Fax
　　　　).Concat(
　　　　 from e in db.Employees
　　　　 select e.HomePhone
　　　　);

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

2. Compound form:var q = (
　　　　 from c in db.Customers
　　　　 select new
　　　　 {
　　　　　　 Name = c.CompanyName,
　　　　　　 c.Phone
　　　　 }
　　　　).Concat(
　　　　 from e in db.Employees
　　　　 select 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.

var q = (
　　　　 from c in db.Customers
　　　　 select c.Country
　　　　).Union(
　　　　 from e in db.Employees
　　　　 select e.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.

var q = (
　　　　 from c in db.Customers
　　　　 select c.Country
　　　　).Intersect(
　　　　 from e in db.Employees
　　　　 select e.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.

var q = (
　　　　 from c in db.Customers
　　　　 select c.Country
　　　　).Except(
　　　　 from e in db.Employees
　　　　 select e.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 are as follows: Performance Enhanced.

Take

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

var q = (
　　from e in db.Employees
　　orderby e.HireDate
　　select e)
　　.Take(5);

Statement Description: select 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.

var q = (
　　from p in db.Products
　　orderby p.UnitPrice descending
　　select p)
　　.Skip(10);

Statement Description: select the 10 most expensive ProductOther 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.

1. Indexvar q = (
　　from c in db.Customers
　　orderby c.ContactName
　　select c)
　　.Skip(50)
　　.Take(10);

Statement Description: The Skip and take operators are used for paging. the first 50 records are skipped and the next 10 records are returned. Therefore, data on the 6th page of the Products table is displayed.

2. sort by unique keyvar q = (
　　from p in db.Products
　　where p.ProductID > 50
　　orderby p.ProductID
　　select p)
　　.Take(10);

Statement Description: Use the WHERE clause and take operator for paging. First, filter the productid with only 50 (the last productid on page 5th) and then sort it by productid. Finally, obtain the first 10 results, therefore, the data on page 6th of the Products table is provided. Note that this method is only applicable to sorting by unique keys.

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.

 

 

var q = from c in db.Customers
　　　　where SqlMethods.Like(c.CustomerID, "C%")
　　　　select c;

For example, if the query consumer ID does not have the "axoxt" format:

var q = from c in db.Customers
　　　　where !SqlMethods.Like(c.CustomerID, "A_O_T")
　　　　select c;Datediffday

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

var q = from o in db.Orders
　　　　where SqlMethods
　　　　.DateDiffDay(o.OrderDate, o.ShippedDate) < 10
　　　　select o;

Statement Description: Query all orders that have been delivered within 10 days after the order is created.

Compiled query operations (Compiled query)

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 control.
VaR loncusts = FN (dB, "London ");
// 3. query the Consumers whose city is Seattle
VaR seacusts = FN (dB, "Seattle ");

Statement Description: In this example, create a compiled query and use it to retrieve the customers in the input city.