[LINQ] sharing: provides an example to demonstrate the real price of the page store on the Database End Of The LINQ to SQL statement.

We previously wroteProgramWhen it comes to paging on the Database End, it is generally difficult to implement it. Especially for SQL 2000 databases, you need to write a bunch of SQL statements or write a stored procedure or something yourself. Now, if you use the LINQ query syntax in vs2008, it will become super simple for developers.

At the course introduced by LINQ last Friday (November 20), we discussedDelayed executionAndDatabase pageHow is it implemented? I tested it with a small example today and shared it with you.

The pagination Syntax of LINQ is as follows:

[ Data source object set]. Skip (index-1) * Pagesize). Take (pagesize)

For example, if a grid shows all records in the sys_users table, 20 records are displayed on each page. To display 5th page data, write the following method:

VaR userlist = CTX. sys_users.skip (( 5 - 1 ) * 20 ). Take ( 20 ); // Query the sys_users table,Skip the first 80 records and retrieve the last 20 records Now let's take a look at the specific execution process from SQL Server Profiler while debugging the following images: We want to query the 3rd and 4 records in the sys_users table. 1. When you run var userlist14 = CTX. sys_users.skip (2). Take (2);, you can see from profiler that no query-related actions have been performed. 2. execute another step. From profiler, we can see that no query-related actions have been performed. This proves that VaR userlist14 = CTX. sys_users.skip (2 ). take (2); this LINQ statement only creates a query and does not execute the query. This is a typical "delayed execution" of LINQ. Only when data is actually used,. 3. execute another step. From profiler, we can see that no query-related actions have been performed. 4. Finally, execute another step to create a databind for the gridview. Now we can see that the query is executed from profiler, and we can see that the compiler helps us set "CTX. sys_users.skip (2 ). take (2) "This simple LINQ query statement is converted into a complicated SQL query statement. The generated SQL statement is: Exec Sp_executesql n ' Select [T1]. [user_id], [T1]. [first_name], [T1]. [last_name], [T1]. [age], [T1]. [birth_date], [T1]. [email], [T1]. [sex]
From (
Select row_number () over (order by [t0]. [user_id], [t0]. [first_name], [t0]. [last_name], [t0]. [age], [t0]. [birth_date], [t0]. [email], [t0]. [sex]) as [row_number], [t0]. [user_id], [t0]. [first_name], [t0]. [last_name], [t0]. [age], [t0]. [birth_date], [t0]. [email], [t0]. [sex]
From [DBO]. [sys_users] as [t0]
) As [T1]
Where [T1]. [row_number] Between @ P0 + 1 and @ P0 + @ p1
Order by [T1]. [row_number] ' , N ' @ P0 int, @ P1 int ' , @ P0 = 2 , @ P1 = 2
The query result is as follows: from the above running conditions, we should be able to get a basic understanding of Delayed execution And Database page The execution principle. Two more points: 1. The above is a test for SQL Server 2005. The generated SQL statement mainly uses the new row_number feature for paging. If SQL Server 2000 does not have row_number, the generated SQL statements are somewhat different. Top is used for paging. The SQL statements generated in SQL 2000 are roughly as follows: Select Top 2 [ T1 ] . [ User_id ] , [ T1 ] . [ First_name ] , [ T1 ] . [ Last_name ] , [ T1 ] . [ Age ] , [ T1 ] . [ Birth_date ] , [ T1 ] . [ Email ] , [ T1 ] . [ Sex ]
From [ DBO ] . [ Sys_users ] As [ T1 ]
Where Not ( Exists (
Select Null As [ Empty ]
From (
Select Top 2 [ T2 ] . [ User_id ]
From [ DBO ] . [ Sys_users ] As [ T2 ]
) As [ T3 ]
Where [ T1 ] . [ User_id ] = [ T3 ] . [ User_id ]
)) Refer: Http://www.cnblogs.com/greater/archive/2008/06/22/1227453.html   2. In addition, if you viewResult View. In addition, the following example shows that when I executed step 2, I checked the value of userlist14, which led to the execution of the query, as shown in: If you have any other questions, you are welcome to discuss them together.