使用SQL Server进行NHibernate分页

当使用SetFirstResult(启动)和SetMaxResults(count)方法来实现分页时,我注意到生成的查询只从some_table中选择一个顶部的计数*,而不考虑start参数,或至少不在数据库级别.看来,如果我指示NHibernate执行以下查询：

var users = session.CreateCriteria<User>()
                   .SetFirstResult(100)
                   .SetMaxResults(5)
                   .List<User>();

105个记录将在数据库服务器和应用程序之间传输,该应用程序将照顾剥离前100个记录.对于包含很多行的表,这可能是一个问题.

我已经验证了使用SQLite 数据库NHibernate利用OFFSET和LIMIT关键字在数据库级别筛选结果.我知道在sql Server 2000中没有相当于OFFSET关键字和Oracle的ROWNUM,但是有没有解决办法？ sql Server 2005/2008如何？

解决方法

Microsoft sql Server使用的sql语言的变体T-sql没有限制条款.它有一个选择顶部{…}修饰符,您看到NHibernate利用sql Server 2000.

使用sql Server 2005,Microsoft引入了Row_Number()(通过{…}命令)函数,可用于替换limit子句,您可以看到NHibernate利用sql Server 2005/2008的优势.

sqlite的查询可能看起来像

select c.[ID],c.[Name]
from [Codes] c
where c.[Key] = 'abcdef'
order by c.[Order]
limit 20 offset 40

而sql Server 2005的类似查询可能看起来像

select c.[ID],c.[Name]
from (
    select c.[ID],c.[Name],c.[Order],[!RowNum] = Row_Number() over (order by c.[Order])
    from [Codes] c
    where c.[Key] = 'abcdef'
) c
where c.[!RowNum] > 40 and c.[!RowNum] <= 60
order by c.[Order]

或者使用Common Table Expressions,它可能看起来像

with
    [Source] as (
        select c.[ID],[!RowNum] = Row_Number() over (order by c.[Order])
        from [Codes] c
        where c.[Key] = 'abcdef'
    )
select c.[ID],c.[Name]
from [Source] c
where c.[!RowNum] > 40 and c.[!RowNum] <= 60
order by c.[Order]

还有一种方法可以在sql Server 2000中执行此操作

select c.[ID],c.[Name]
from (
    select top 20 c.[ID],c.[Order]
    from (
        select top 60 c.[ID],c.[Order]
        from [Codes] c
        where c.[Key] = 'abcdef'
        order by c.[Order]
    ) c
    order by c.[Order] desc
) c
order by c.[Order]

使用SQL Server进行NHibernate分页

解决方法

猜你在找的MsSQL相关文章