Active Record或NHibernate生成无效的Sql以在SqlQuery上进行分页 [英] Active Record or NHibernate generating invalid Sql for paging on SqlQuery

问题描述

在使用nHibernate 2.1.1 GA和ActiveRecord 2.0.1的ActiveRecord SqlQuery应用分页(使用SetFirstResult和SetMaxResults)时，将生成以下sql:

When applying Paging (using SetFirstResult and SetMaxResults) to an ActiveRecord SqlQuery, with nHibernate 2.1.1 GA and ActiveRecord 2.0.1 the following sql is generated:

SELECT 
    TOP 40 
FROM 
    (, ROW_NUMBER() OVER(ORDER BY account.Name, account.State) as __hibernate_sort_row 
        select  account.Name 
                <rest of query>
    ) as query 
WHERE query.__hibernate_sort_row > 40 
ORDER BY query.__hibernate_sort_row

此错误，而且不在sql中运行...而应该在

This errors and moreover doesn't run in sql... whereas it should be

SELECT TOP 40  * 
FROM ( 
    SELECT
        ROW_NUMBER() OVER (ORDER BY account.Name, account.State) as __hibernate_sort_row 
    ,select  account.Name 
                <rest of query>
) as query 
WHERE query.__hibernate_sort_row > 40 
ORDER BY query.__hibernate_sort_row

奇怪的是:

• 没有分页的查询工作正常
• 使用分页时，第1页工作正常(即第一个结果= 0，maxresult = 40)
• 完全相同的方法适用于HqlQuery，只有SqlQuery受此影响.

• The query without paging works fine
• With paging, page 1 works fine (i.e. first result = 0, maxresult = 40)
• Exactly the same approach works fine for HqlQuery, only SqlQuery affected.

这适用于MS2005Dialect和MS2008Dialect ...

This applies to MS2005Dialect and MS2008Dialect...

有人知道我的愚蠢问题吗?

Anyone know my stupid issue ?

推荐答案

好，我已经找到此页面

Well I've found this page Possible SQL Server bug which, contra to the title, suggests I'll need to write it in my sql. No problem just could have done without spending half a day trying.

任何人都可以确认确实如此吗?

Can anyone confirm this is definitely the case ?

这篇关于Active Record或NHibernate生成无效的Sql以在SqlQuery上进行分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！