利用 LINQ to SQL 對資料做分頁時應注意的事 ( 重要 )(转)

http://blog.miniasp.com/post/2009/06/The-important-LINQ-to-SQL-Paging-issues.aspx#

 

熟悉 LINQ to SQL 的朋友應該很清楚如何透過 Skip 與 Take 方法來取得資料的部分集合，但各位可能不知道透過這種方式分頁時有個很有可能出錯的地方，而且這個錯誤可能會讓你覺得這是 LINQ to SQL 的 Bug，有在使用 LINQ to SQL 分頁的人必看此篇文章。

假設我們有兩個表格定義如下：

Table_1

CREATE TABLE [dbo].[Table_1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [CreateTime] [datetime] NOT NULL CONSTRAINT [DF_Table_1_CreateTime]  DEFAULT (getdate()),
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

Table_2

CREATE TABLE [dbo].[Table_2](
    [Name] [nvarchar](50) NOT NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CreateTime] [datetime] NOT NULL CONSTRAINT [DF_Table_2_CreateTime]  DEFAULT (getdate()),
 CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

各位應該可以看到這兩個表格幾乎一模一樣，差別只在於 ID 所在的位置不同而已：

Table_1

• ID ( P.K. )  int  ( 自動編號 )
• Name
• CTime

Table_2

• Name
• ID ( P.K. ) int  ( 自動編號 )
• CTime

然後我個別新增 10 筆測試資料 ( 以下語法執行 10 次 )：

INSERT INTO [TESTDB].[dbo].[Table_1] ([Name]) 
VALUES('User ' + cast(cast(rand() * 100 as int) as varchar(3)))

INSERT INTO [TESTDB].[dbo].[Table_2] ([Name]) 
VALUES('User ' + cast(cast(rand() * 100 as int) as varchar(3)))

 

然後我寫了個很簡單的測試程式 (Console Application)，我設定每 5 筆資料一頁，分別取出兩頁的資料：

using System;
using System.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            MyDataContext db = new MyDataContext();

            db.Log = Console.Out;

            var q1 = from p in db.Table_1 select p;
            var q2 = from p in db.Table_2 select p;

            Console.WriteLine();
            Console.WriteLine("Table_1: Show T-SQL in page1 & page2");
            Console.WriteLine();

            var page1 = q1.Skip(0).Take(5).ToList();
            var page2 = q1.Skip(5).Take(5).ToList();

            Console.WriteLine();
            Console.WriteLine("Table_2: Show T-SQL in page1 & page2");
            Console.WriteLine();

            var t2_page1 = q2.Skip(0).Take(5).ToList();
            var t2_page2 = q2.Skip(5).Take(5).ToList();
        }
    }
}

 

執行下去你就會知道，在 Table_1 的分頁結果中，第一頁的資料與第二頁的資料是連貫的：

再來，你就會知道，在 Table_2 的分頁結果中，第一頁的資料與第二頁的資料就開始出現重複的資料：

從上述結果你可以看到，我將 LINQ to SQL 在 SQL Server 中實際查詢的 T-SQL 指令也都倒出來看，你可以發現當你的 LINQ to SQL 語法沒有先進行排序時，會將依照 SQL Server 中所有欄位的出現順序來進行排序，然後依照這個排序進行分頁取得資料！所以當利用 Skip 與 Take 進行分頁時，就會發現分頁後的資料竟然是錯的，而且只有第一頁的資料與任何超過第二頁的資料會有可能出現重複的資料，而這就是最大的陷阱所在！正在使用 LINQ to SQL 的人務必要特別注意這個小細節！

終極結論

• 當資料表中的第一個欄位是 Primary Key 時，預設可以不用先排序就可以取得正確的資料。
• 當資料表中的 Primary Key 不是出現在第一個欄位時，要使用 Skip 與 Take 方法前，記得要先利用 OrderBy 排序過，然後再進行分頁操作，否則就跟我們一樣遇到抓錯資料的狀況。