How can I page through data using nHibernate using a CTE?

Question

Is there any example code someone can show me that will page through data in a given table, and use CTE's under the hood?

Is nhibernate suppose to work across various databases out of the box? I am using MS SQL Server 2008 now, but what if the client wants to use Oracle someday?

Answer 1

For paging you just define start and end result in the query:

IQuery q = sess.CreateQuery("from DomesticCat cat");
q.SetFirstResult(20);
q.SetMaxResults(10);
IList cats = q.List();

See the reference docs

NHibernate is supposed to work across various databases. Many dialects are implemented and available out of the box. SqlServer and Oracle are both very well supported.

See the reference docs for a list of supported dialects.