posts - 930,  comments - 588,  views - 402万
< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

      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   PetterLiu  阅读(5318)  评论(4编辑  收藏  举报
编辑推荐:
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
阅读排行:
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· 【.NET】调用本地 Deepseek 模型
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
点击右上角即可分享
微信分享提示