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

问题描述

当使用 SetFirstResult(start) 和 SetMaxResults(count) 方法实现分页时，我注意到生成的查询只执行 select top count *来自 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.

我已经通过 SQLite 数据库验证，NHibernate 利用了 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.

在 SQL Server 2005 中，Microsoft 引入了 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屋！