LINQ to SQL statements (9) Top/bottom and paging and sqlmethods

Application scenario: The appropriate 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.

var q = (    from in  db. Employees      e.hiredate    Select  e)    . Take (5);

Statement 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.

var q = (    from in  db. Products      p.unitprice Descending    select  p)    . Skip (ten);

Statement Description: Select All products except 10 of the most expensive products.

TakeWhile

To 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

Note: 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

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

1. Index

var q = (    from in  db. Customers      c.contactname    Select  c)    . Skip ()    . Take (ten);

Statement 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 = (    from in  db. Products    where      p.productid    Select  p)    . Take (ten);

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

SqlMethods operation

In LINQ to SQL statements, we are provided with a sqlmethods operation, which is further convenient for us, such as the like method used for custom wildcard expressions, and equals is used to compare equality.

Like

A custom wildcard expression. % represents a 0-length or any-length string; _ denotes a character; [] denotes a character in a range; [^] denotes a character that is not in a range. For example, query consumer ID with "C" beginning of the consumer.

var  from inch db. Customers        where"c%")        select C;

For example, query consumer ID does not have "axoxt" form of consumer:

var  from inch db. Customers        where"a_o_t")        Select C;

Datediffday

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

var  from inch db. Orders        where  sqlmethods                select o;

Statement Description: Queries all orders that have been shipped within 10 days of the creation of the order.

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 for the input city.

LINQ to SQL statements (9) Top/bottom and paging and sqlmethods