NHibernate分页与SQL Server [英] NHibernate paging with SQL Server

问题描述

当使用SetFirstResult(start)和SetMaxResults(count)方法实现分页时，我注意到生成的查询仅执行select top count * from some_table，并且没有考虑start参数，或者至少不在数据库级别.看来，如果我指示NHibernate执行以下查询:

When using SetFirstResult(start) and SetMaxResults(count) methods to implement paging I've noticed that the generated query only does a select top count * from some_table and it does not take the start parameter into account or at least not at the database level. It seems that if I instruct NHibernate to execute the following query:

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

105条记录将在数据库服务器和应用程序之间传输，它将小心剥离前100条记录.对于包含许多行的表，这可能是个问题.

105 records will transit between the database server and the application which will take care to strip the first 100 records. With tables containing many rows this could be a problem.

我已经证实，NHibernate使用 SQLite 数据库可以利用OFFSET和LIMIT关键字以在数据库级别过滤结果.我知道在SQL Server 2000中没有OFFSET关键字和Oracle的ROWNUM等效项，但是有什么解决方法吗? SQL Server 2005/2008怎么样?

I've verified that with an SQLite database NHibernate takes advantage of the OFFSET and LIMIT keywords to filter results at the database level. I am aware that there's no equivalent of the OFFSET keyword and Oracle's ROWNUM in SQL Server 2000 but is there any workaround? How about SQL Server 2005/2008?

推荐答案

T-SQL(Microsoft SQL Server使用的SQL语言的变体)没有limit子句.它有一个select top {...}修饰符，您可以看到NHibernate在SQL Server 2000中加以利用.

T-SQL, the variant of the SQL language which Microsoft SQL Server uses, does not have a limit clause. It has a select top {...} modifier which you see NHibernate taking advantage of with SQL Server 2000.

Microsoft在SQL Server 2005中引入了Row_Number() over (order by {...})函数，该函数可以替代limit子句，并且您可以看到NHibernate在SQL Server 2005/2008中利用了该函数.

With SQL Server 2005, Microsoft introduced the Row_Number() over (order by {...}) function which can be used as a replacement to the limit clause, and you can see NHibernate taking advantage of that with SQL Server 2005/2008.

对SQLite的查询可能看起来像

A query for SQLite might look like

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

对于SQL Server 2005的类似查询可能看起来像

while a similar query for SQL Server 2005 might look like

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]

或者，使用通用表表达式，它可能看起来像

or, using Common Table Expressions, it might look like

with
    [Source] as (
        select c.[ID], c.[Name], c.[Order]
            , [!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中也可以做到这一点

There is a way to do it in SQL Server 2000 as well

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

这篇关于NHibernate分页与SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！