posts - 571,  comments - 558,  trackbacks - 20

      Linq to sql 与 Linq to entities 生成的SQL Script与分页实现,我们选用Northwind做演示的Database.

First 5 Records (Page 1)

   1:  (from c in Customers
   2:          select new {
   3:              c.City,
   4:              c.ContactName
   5:          }).Skip(0).Take(5)


Generated SQL

   1:  SELECT TOP (5) [t0].[City], [t0].[ContactName]
   2:  FROM [Customers] AS [t0]
 

Result set

City            ContactName
--------------- ------------------------------
Berlin          Maria Anders
México D.F.     Ana Trujillo
México D.F.     Antonio Moreno
London          Thomas Hardy
Luleå           Christina Berglund





Next 5 Records (Page 2)

   1:  (from c in Customers
   2:          select new {
   3:              c.City,
   4:              c.ContactName
   5:          }).Skip(5).Take(5)


Generated SQL

   1:  DECLARE @p0 Int = 5
   2:  DECLARE @p1 Int = 5
   3:   
   4:  SELECT [t1].[City], [t1].[ContactName]
   5:  FROM (
   6:      SELECT ROW_NUMBER() OVER (ORDER BY [t0].[City], [t0].[ContactName]) AS [ROW_NUMBER], 
   7:      [t0].[City], [t0].[ContactName]
   8:      FROM [Customers] AS [t0]
   9:      ) AS [t1]
  10:  WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
  11:  ORDER BY [t1].[ROW_NUMBER]


Result set

City            ContactName
--------------- ------------------------------
Barquisimeto    Carlos González
Bergamo         Giovanni Rovelli
Berlin          Maria Anders
Bern            Yang Wang
Boise           Jose Pavarotti
 
    由此可以,可以用skip,take方法来实现分页,但也许你注意到上面有一条记录出现了两次: Berlin    Maria Anders,这是因为我们没有排序的原因。
在Linq to entities中也有这个问题,并且强制要求你要排序,否则throw出这个异常:
 

The method 'Skip' is only supported for sorted input in LINQ to Entities.
The method 'OrderBy' must be called before the method 'Skip'.

据说这个问题已经在.NET 4.0 framework修正了。

   1:  (from c in Customers 
   2:  orderby c.ContactName        
   3:  select new 
   4:  {c.City, c.ContactName }).Skip(0).Take(5)
 
Generated SQL

   1:  DECLARE @p0 Int = 0
   2:  DECLARE @p1 Int = 5
   3:   
   4:  SELECT [t1].[City], [t1].[ContactName]
   5:  FROM (
   6:      SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ContactName]) AS [ROW_NUMBER], [t0].[City], [t0].[ContactName]
   7:      FROM [Customers] AS [t0]
   8:      ) AS [t1]
   9:  WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
  10:  ORDER BY [t1].[ROW_NUMBER]


可以看出也用ROW_NUMBER实现的。另外,如果你不写Ordery,Linq to sql 生成的sql script将会非常不易阅读。

最后调用的CSharp代码可以是:

            int totalcount = dboperator.Customers.Count();
            int pagesize = 10;
            int pages = (int)Math.Round((double)totalcount / pagesize);
 
            for (int pageindex = 1; i <= pages; i++)
            {
                var customers = dboperator.Customers.OrderBy(c => c.ContactName)
                                   .Skip((pageindex - 1) * pageSize)
                                   .Take(pageSize);
 
                //...
            }


希望这篇Post对您有帮助.
Author: Petter Liu    http://wintersun.cnblogs.com
posted on 2009-10-06 11:27  PetterLiu  阅读(...)  评论(... 编辑 收藏