NHibernate SQLquery 用于分页到 HQL / ICriteria
我正在执行一个查询，根据 row_number() 排序并返回 X 行 我正在将 NHibernate 与 MSSQL 一起使用，并且尝试使用 CreateSQLQuery 来进行分页工作，并且我有以下查询：
select s.*
from(
select distinct release.[stop], survey.SurveyId, survey.Created, survey.CopyOfId, survey.DesignTemplateId, survey.UserId, survey.Template, [Row] = Row_Number() over (order by survey.[SurveyId])
from Survey as survey
inner join Release as release on release.SurveyId = survey.SurveyId
group by survey.SurveyId
, survey.Created
, survey.CopyOfId
, survey.DesignTemplateId
, survey.UserId
, survey.Template
, release.[stop]
) as s
where s.[Row] >= 0 and s.[Row] <= 20
order by s.[stop]
有谁知道如何使用 HQL 或 ICriteria （甚至更好）而不是普通 SQL 来进行此工作？这样做的原因是我想要一个与 SQLite 和 MS SQL Server 2005 兼容的查询，以便我可以使用 .SetMaxResult() og .SetFirstResult()
提前致谢！
I'm making a query that sorts and returns X rows based on row_number()
I'm using NHibernate with MSSQL and im trying to get paging working using CreateSQLQuery and i have this query:
select s.*
from(
select distinct release.[stop], survey.SurveyId, survey.Created, survey.CopyOfId, survey.DesignTemplateId, survey.UserId, survey.Template, [Row] = Row_Number() over (order by survey.[SurveyId])
from Survey as survey
inner join Release as release on release.SurveyId = survey.SurveyId
group by survey.SurveyId
, survey.Created
, survey.CopyOfId
, survey.DesignTemplateId
, survey.UserId
, survey.Template
, release.[stop]
) as s
where s.[Row] >= 0 and s.[Row] <= 20
order by s.[stop]
does anyone know how to get this working using HQL or ICriteria (even better) instead of plain SQL? The reason for this is that I want a query that is compatible with both SQLite and MS SQL Server 2005, so that i can use .SetMaxResult() og .SetFirstResult()
Thanks in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
尽量避免在 nHibernate 中使用纯 SQL。
在 Criteria 对象上，使用 SetFirstResult() 和 SetMaxResult() 进行分页。
每页 10 条记录 ?第一页是 criteria.SetFirstResult(0).SetMaxResult(10)，第三页是 criteria.SetFirstResult(20).SetMaxResult(10)
始终使用正确的方言。例如，SQL Server 2008 比 SQL Server 2005 具有更多的分页功能。
Try to avoid using plain SQL in nHibernate.
On Criteria object, use SetFirstResult() and SetMaxResult() for your paging.
Pages of 10 records ? First page is criteria.SetFirstResult(0).SetMaxResult(10) and third page is criteria.SetFirstResult(20).SetMaxResult(10)
Always use the correct dialect. For exemple SQL Server 2008 has more paging features than SQL Server 2005.
这是一篇很棒的文章，可以做你想做的事
http://www.tobinharris.com/过去/2008/10/20/almost-iqueryable-with-nhibernate-hql/
Here's an excellent article to do what you want
http://www.tobinharris.com/past/2008/10/20/almost-iqueryable-with-nhibernate-hql/