NHibernate SQLquery for paging to HQL / ICriteria

Question

Hello everyone!

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!

Answer 1

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.