Go Union All/union/intersect and Top/bottom and paging and sqlmethods,skip,take,takewhile,skipwhile in C#linq, compiling queries, etc.

This article transferred from: http://www.cnblogs.com/suizhikuo/p/3791799.html

Let's go on to the LINQ to SQL statement, which we'll talk about Union all/union/intersect operations and top/bottom operations and paging operations and sqlmethods operations. Union All/union/The intersect operation applies to scenarios where two collections are processed, such as appending, merging, fetching the same items, intersecting items, and so on. Concat (Connection) Description: Joins different collections, does not automatically filter the same items; 1. Simple form:varQ = (   fromCinchdb. CustomersSelectc.phone). Concat ( fromCinchdb. CustomersSelectc.fax). Concat ( fromEinchdb. EmployeesSelecte.homephone); Statement description: Returns the phone and fax for all consumers and employees. 2. Composite form:varQ = (   fromCinchdb. CustomersSelect New{Name=c.companyname, C.phone}). Concat ( fromEinchdb. EmployeesSelect New{Name= E.firstname +" "+E.lastname, Phone=E.homephone}); Statement Description: Returns the name and phone number of all consumers and employees. Union (merge) Description: Joins different collections, automatically filters the same items; That is, merging two collections to filter the same items. varQ = (   fromCinchdb. CustomersSelectc.country). Union ( fromEinchdb. EmployeesSelecte.country); Statement Description: Query the country where the customer and the employee are located. Intersect (Intersection) Description: Take intersect item; delay. That is, get the same item (intersection) of different sets. That is, it iterates through the first collection, finds all the unique elements, then iterates through the second set and compares each element to the element found earlier, returning all elements that appear within the two collection. varQ = (   fromCinchdb. CustomersSelectc.country). Intersect ( fromEinchdb. EmployeesSelecte.country); Statement Description: Query the country where the customer and the employee are in. Except (and non) Description: Exclude intersecting items; That is, you remove the same item from a collection as in another collection. Iterates through the first collection, finds all the unique elements, and then iterates through the second collection, returning all the elements in the second collection that do not appear in the preceding collection of elements. varQ = (   fromCinchdb. CustomersSelectc.country). Except ( fromEinchdb. EmployeesSelecte.country); Statement Description: Query the customer and the staff of different countries. Top/Bottom Operation applicable scenario: the right amount of the data you want to take out, not all out, so performance has been enhanced. Take description: Gets the first n elements of a collection; That is, only a limited number of result sets are returned. varQ = (   fromEinchdb. Employees bye.hiredateSelecte). Take (5Statement Description: Select the first 5 employees that are hired. Skip Description: Skips the first n elements of a collection; That is, we skip the given number and return the result set later. varQ = (   fromPinchdb. Products byP.unitprice DescendingSelectp). Skip (TenStatement Description: Select All products except 10 of the most expensive products. TakeWhile: Stop acquiring until a certain condition is established; That is, it uses its condition to determine the elements in the source sequence sequentially, and returns the element that conforms to the judging condition, which will end at the end of the return false or source sequence. SkipWhile Description: Stop skipping until a certain condition is established; That is, by using its condition to determine the elements in the source sequence and skipping the first element that matches the criteria, once the judgment returns False, the next step is to stop judging and return all the remaining elements. Paging (paging) operation applicable scenario: combine skip and take to achieve data paging operations. 1. index var q = (   fromCinchdb. Customers byC.contactnameSelectc). Skip ( -)  . Take (TenStatement Description: Use the Skip and take operators for paging, skip the first 50 records, and return to the next 10 records, so provide the data that displays the 6th page of the Products table. 2. Sort by Unique key var q = (   fromPinchdb. ProductswhereP.productid > -   byP.productidSelectp). Take (TenStatement Description: Using the WHERE clause and the take operator for paging, first filtering out the ProductID of only 50 (the last ProductID of the 5th page), then sorting by ProductID, and finally fetching the first 10 results, Therefore, the data on page 6th of the Products table are provided. Note that this method only applies if you are sorting by unique key. The sqlmethods operation provides us with sqlmethods operations in LINQ to SQL statements, further providing convenience to us, such as the like method used for custom wildcard expressions, and equals for comparison of equality. A wildcard expression like a custom. % represents 0 length or any length of string; _ denotes a character; [] denotes a character in a range; [^] Represents a character that is not in a range of ranges. For example, query consumer ID with "C" beginning of the consumer. varQ = fromCinchdb. CustomerswhereSqlmethods.like (C.customerid,"c%")  SelectC; For example, query consumer ID without "AXOXT" consumer:varQ = fromCinchdb. Customerswhere! Sqlmethods.like (C.customerid,"a_o_t")  Selectc;datediffday Description: Compare between two variables. respectively: Datediffday, Datediffhour, Datediffmillisecond, Datediffminute, Datediffmonth , Datediffsecond, DateDiffYearvarQ = fromOinchdb. OrderswhereSqlMethods. Datediffday (O.orderdate, o.shippeddate)<Ten  Selecto Statement Description: Query after the order is createdTenall orders that have been shipped within the day. Compiled query operation (Compiled query) Description: Before we had a good way to edit the written SQL statements to re-query, now we can do this, see the following example://1. Create compiled queryNorthwindDataContext db =NewNorthwindDataContext ();varfn =Compiledquery.compile (northwinddatacontext DB2,stringCity) = fromCinchDB2. CustomerswhereC.city = = CitySelectc); //2. Query the city for the London consumer, represented by the Loncusts collection, which can be bound with the data controlvarloncusts = FN (db,"London"); //3. Query the city for Seattle consumersvarseacusts = FN (db,"Seattle"Statement Description: This example creates a compiled query and then uses it to retrieve the customer that entered the city. Original address: http://blog.csdn.net/aladdinty/article/details/3599878

Go Union All/union/intersect and Top/bottom and paging and sqlmethods,skip,take,takewhile,skipwhile in C#linq, compiling queries, etc.