NHibernate实现查询分页

NHibernate是一个O/RM框架，在增删改查等操作时我们可以直接在程序中编写语句实现不用再去写SQL语句。查询所有记录，在NHibernate中是很容易实现的，不过分页的实现就不能像在数据库中直接写SQL语句那样方便了。

实现分页时主要利用了Skip和Take方法。具体代码如下。

public IList<T> LoadByPage(int pageIndex, int pageSize,out int totalCount)
        {
            using (ISession session = sessionFactory.OpenSession())
            {
                totalCount = session.QueryOver<T>().RowCount();	//总条数
                return session.QueryOver<T>().Skip((pageIndex - 1) * pageSize).Take(pageSize).List();
            }
        }

运行时生成的SQL语句与结果，如下。

测试名称:	LoadByPageTest
测试结果:	已通过
结果 的标准输出:	
NHibernate: SELECT count(*) as y0_ FROM T_Product this_	--总行数

--第一页，由此可见看到如果是第一页，NHibernate只是简单的利用TOP()函数选出页面需要的条数的记录
NHibernate: SELECT TOP (@p0) this_.ID as ID1_1_0_, this_.Code as Code2_1_0_, this_.Name as Name3_1_0_, this_.QuantityPerUnit as Quanti4_1_0_, this_.Unit as Unit5_1_0_, this_.SellPrice as SellPr6_1_0_, this_.BuyPrice as BuyPri7_1_0_, this_.Remark as Remark8_1_0_, this_.BusinessID as Busine9_1_0_ FROM T_Product this_;@p0 = 2 [Type: Int32 (0:0:0)]

--结果
电脑
手机

NHibernate: SELECT count(*) as y0_ FROM T_Product this_	--总行数

--第二页，从第二页开始NHibernate利用了ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP)函数来实现分页，如果是在低版本中sql2005及更低版本中应该是另外一种方式实现了。
NHibernate: SELECT TOP (@p0) ID1_1_0_, Code2_1_0_, Name3_1_0_, Quanti4_1_0_, Unit5_1_0_, SellPr6_1_0_, BuyPri7_1_0_, Remark8_1_0_, Busine9_1_0_ FROM (SELECT this_.ID as ID1_1_0_, this_.Code as Code2_1_0_, this_.Name as Name3_1_0_, this_.QuantityPerUnit as Quanti4_1_0_, this_.Unit as Unit5_1_0_, this_.SellPrice as SellPr6_1_0_, this_.BuyPrice as BuyPri7_1_0_, this_.Remark as Remark8_1_0_, this_.BusinessID as Busine9_1_0_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM T_Product this_) as query WHERE query.__hibernate_sort_row > @p1 ORDER BY query.__hibernate_sort_row;@p0 = 2 [Type: Int32 (0:0:0)], @p1 = 2 [Type: Int32 (0:0:0)]

--结果
笔记本电脑
平板电脑

调用。

public List<ProductVM> GetListByPage(int pageIndex,int pageSize)
        {
            int totalCount;	//总条数
            var productIList = productDao.LoadByPage(pageIndex, pageSize,out totalCount);
            List<ProductVM> productListByPage = (from p in productIList
                                                 select new ProductVM
                                                 {
                                                     ID = p.ID,
                                                     Code = p.Code,
                                                     Name = p.Name,
                                                 }).ToList();
            return productListByPage;
        }