LINQ Experience (8)--LINQ to SQL statement Union All/union/intersect and Top/bottom and paging and sqlmethods

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/intersect operation

Scenario: two sets of processing, such as append, merge, take the same items, intersect items and so on.

Concat (Connection)

Description: Connect a different collection. Do not actively filter the same; delay.

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: Returns the phone and fax of all consumers and employees.

2. Composite form:

var  q = (from  C in  db. Customers select new  {Name = C.companyname, C.pho NE}). Concat (from  e in  db. Employees select new  {Name = e.firstname +  + e.lastname, Phone = E.homephone}); 

Statement Description: Returns the name and telephone number of all consumers and employees.

Union (Consolidated)

Description: Connect a different collection. Actively filter the same items yourself. Delay.

That is, merging two collections to filter the same items.

Q = (         db. Customers         c.country        ). Union (         db. Employees         e.country        );

Statement Description: Query the country where the customer and the employee are located.

Intersect (intersect)

Description: Take intersect item; delay.

That is to get the same item (intersection) of different sets. That is, the first collection is traversed, all the unique elements are found, and then the second collection is traversed. Each element is compared with the element found earlier. Returns all elements that appear within the two collection.

Q = (         db. Customers         c.country        ). Intersect (         db. Employees         e.country        );

Statement Description: Query the country where the customer and the employee are in.

Except (with non)

Description: Exclude intersecting items; That is, delete the same item from a collection as there is a collection. Iterate through the first collection, find all the unique elements, and then traverse the second collection. Returns all elements in the second collection that are not present in the previously derived elements collection.

Q = (         db. Customers         c.country        ). Except (         db. Employees         e.country        );

Statement Description: Query the customer and staff of different countries.

Top/bottom operation

Application scenario: Take out the appropriate amount of data you want, not all of it. This improves performance.

Take

Description: Gets the first n elements of a collection;

That is, only a limited number of result sets is returned.

Q = (    db. Employees        e.hiredatee)    . Take (5);

Statement Description: Select the first 5 employees employed.

Skip

Description: Skips the first n elements of a collection;

That is, we skip the given number and return the result set later.

Q = (    db. Products    descending    p).    Skip (10);

Statement Description: Choose from 10 of the most expensive products.

TakeWhile

Description: Stop acquisition until a certain condition is established. Delay. That is, using its condition to infer elements in the source sequence in turn, returns an element that conforms to the inferred condition, which will return false or end at the end of the source sequence.

SkipWhile

Note: Stop skipping until a certain condition is established. Delay. That is, using its condition to infer elements in the source sequence and to skip the first element that conforms to the inference condition, once the inference returns false, then the inference is no longer inferred and all the remaining elements are returned.

Paging (paging) operation

Application scenario: The data paging operation can be realized by combining skip and take.

1. Index

Q = (    db. Customers        c.contactnamec)    . Skip ()    . Take (10);

Statement Description: Use the Skip and take operators for paging, skip the first 50 records, and then return to the next 10 records. Therefore, the data showing the 6th page of the Products table is provided.

2. Sort by Unique key

Q = (    db. Products    p.productid >    p.productid    p)    . Take (10);

Statement description: Using the WHERE clause and the take operator for paging, first filter to get only 50 (the last ProductID of the 5th page) above the ProductID, and then sort by ProductID. Finally, the first 10 results are taken. 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.

SqlMethods operation

In LINQ to SQL statements, we are provided with sqlmethods operations. Further, for example, the like method is used to define a wildcard expression for itself, and equals is used to compare equality.

Like

A wildcard expression of your own definition.

% represents a 0-length or random-length string. _ Represents a character. [] represents a character in a range of ranges. [^] denotes a character that is not in a range of ranges. For example, query consumers whose consumer ID begins with "C".

Db. Customers        sqlmethods"c%")        C;

For example, the consumer ID is not "Axoxt" in the form of consumers:

Db. Customers        !  SqlMethods"a_o_t")        C;

Datediffday

Description: Compare between two variables. respectively: Datediffday, Datediffhour, Datediffmillisecond, Datediffminute, Datediffmonth, Datediffsecond, DateDiffYear

Db. Orders        sqlmethods        . Datediffday (O.orderdate, o.shippeddate) < ten        o;

Statement Description: Queries all orders that have been shipped within 10 days after the order was created.

Compiled query operation (Compiled query)

Description: We did not have a good way to edit and query the written SQL statements before. Now we can do this by looking at the following example:

//1. Creating a compiled queryNorthwindDataContextdb =NewNorthwindDataContext();varfn =CompiledQuery. Compile ((NorthwindDataContextDB2,stringCity) = fromCinchDB2. CustomerswhereC.city = = CitySelectc);//2. Querying the City of London for consumers, represented by a loncusts collection, can then be bound with data controlsvarloncusts = FN (db,"London");//3. Querying the city for Seattle consumersvarseacusts = FN (db,"Seattle");

Statement Description: This sample creates a compiled query and then uses it to retrieve the customer for the input city.

LINQ Experience (8)--LINQ to SQL statement Union All/union/intersect and Top/bottom and paging and sqlmethods