使用LINQ分页时需要注意的一个细节

it2022-06-24  94

昨天在用LINQ写分页的时候碰到一个很奇怪的问题:翻页的时候,有的数据会莫名其妙地消失,查了半个多小时才发现问题所在,其实是一个很细节的地方。

数据表如下:

LINQ分页的实现是:

 

var articles  =  context.Articles.Skip(startRecord).Take(pageSize);

 

pageSize不为1时,得出的结果总是错的。用Profiler查看后,发现当pageSize1时,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


最新回复(0)