Active Record 或 NHibernate 为 SqlQuery 上的分页生成无效的 Sql
使用 nHibernate 2.1.1 GA 和 ActiveRecord 将分页（使用 SetFirstResult 和 SetMaxResults）应用于 ActiveRecord SqlQuery 时2.0.1 生成以下 sql：
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 中运行...而它应该是
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受到影响。
这适用于 MS2005Dialect 和 MS2008Dialect...
有人知道我的愚蠢问题吗？
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
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
The odd things are :
- 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, onlySqlQueryaffected.
This applies to MS2005Dialect and MS2008Dialect...
Anyone know my stupid issue ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
好吧，我找到了这个页面 可能的 SQL Server 错误，与标题相反，这表明我需要将其写入我的 sql 中。不花半天时间尝试就可以解决任何问题。
任何人都可以确认这确实是这样吗？
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 ?
我为此使用了一个解决方法。这是链接。可能对你有帮助。
I used a work-around for it. Here its link. May be helpful to you.