昨天在用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 ]这样的话结果就正确了。看样子以后这种细节问题还得多注意一下。
转载于:https://www.cnblogs.com/yzlhccdec/archive/2008/03/06/1093750.html