http://www.cnblogs.com/yzlhccdec/archive/2008/03/06/1093750.html
昨天在用 LINQ 写分页的时候碰到一个很奇怪的问题:翻页的时候,有的数据会莫名其妙地消失,查了半个多小时才发现问题所在,其实是一个很细节的地方。
数据表如下 :
LINQ分页的实现是:
var articles
=
context.Articles.Skip(startRecord).Take(pageSize);
当 pageSize不为 1时,得出的结果总是错的。用 Profiler查看后,发现当 pageSize为 1时, LINQ生成的语句为:
SELECT
TOP
(
10
)
[
t0
]
.
[
Id
]
,
[
t0
]
.
[
Content
]
,
[
t0
]
.
[
PublishTime
]
FROM
[
dbo
]
.
[
Article
]
AS
[
t0
]
当 pageSize不为 1是, LINQ生成的语句为:
SELECT
[
t1
]
.
[
Id
]
,
[
t1
]
.
[
Content
]
,
[
t1
]
.
[
PublishTime
]
FROM
(
SELECT
ROW_NUMBER()
OVER
(
ORDER
BY
[
t0
]
.
[
Id
]
,
[
t0
]
.
[
PublishTime
]
)
AS
[
ROW_NUMBER
]
,
[
t0
]
.
[
Id
]
,
[
t0
]
.
[
Content
]
,
[
t0
]
.
[
PublishTime
]
FROM
[
dbo
]
.
[
Article
]
AS
[
t0
]
)
AS
[
t1
]
WHERE
[
t1
]
.
[
ROW_NUMBER
]
BETWEEN
@p0
+
1
AND
@p0
+
@p1
ORDER
BY
[
t1
]
.
[
ROW_NUMBER
]
如果聚集索引刚好建立在 Id字段上面,这样做是没有任何问题的。但我恰好把聚集索引建立到了 PublishTime 上面。如此一来,当执行语句一的时候,分页是根据聚集索引进行排序的,但是执行语句二的时候,分页是根据 Id排序的,所以就出现了数据“消失”的情况。弄清楚原因后,解决起来就简单啦,直接加一个排序字段就行啦。修改后的 LINQ分页实现如下:
var articles
=
context.Articles.OrderBy(p
=>
p.PublishTime).Skip(startRecord).Take(pageSize);
生成的 SQL语句也会变为
SELECT
[
t1
]
.
[
Id
]
,
[
t1
]
.
[
Content
]
,
[
t1
]
.
[
PublishTime
]
FROM
(
SELECT
ROW_NUMBER()
OVER
(
ORDER
BY
[
t0
]
.
[
PublishTime
]
)
AS
[
ROW_NUMBER
]
,
[
t0
]
.
[
Id
]
,
[
t0
]
.
[
Content
]
,
[
t0
]
.
[
PublishTime
]
FROM
[
dbo
]
.
[
Article
]
AS
[
t0
]
)
AS
[
t1
]
WHERE
[
t1
]
.
[
ROW_NUMBER
]
BETWEEN
@p0
+
1
AND
@p0
+
@p1
ORDER
BY
[
t1
]
.
[
ROW_NUMBER
]
这样的话结果就正确了。看样子以后这种细节问题还得多注意一下。