Is it possible to implement paging type queries with NHibernate?

I'd like to implement custom paging within a DataGrid that is bound to a very large resultset and wish to cut down the rows returned by NHibernate. e.g. Some form of query that can restrict the result set to rows 130000 to 130050?

Yes, the IQuery interface has SetFirstResult and SetMaxResults methods. On SQL Server this isn't quite optimal (yet) because the all records are queried and then filtered. With most other databases, some SQL construct is used to filter the results (LIMIT, ROWNUM) and that's far more efficient.

Thanks. Tried it and it worked though a pity it isn't even quicker on SQL Server...

...and it won't be either. Unfortunately that's a limitation of SQL Server 2000 and below - the only way to DB side paging is through stored procedures. NHibernate does it's paging by getting the entire match set but only instantiating the objects for the page you want.

Can't speak for the next version of SQL Server though...[/quote]

SQL Server 2005 has a rownum construct that will make db-paging possible.

We've implemented inherited mssql dialect supporting paging through triple sorted subqueries. It performs quite well as first pages of the result are requested.

If anybody is interested, I can provide the source code of the dialect.

urmorae, could you please post your code?

Here it is:

http://urmo.mindworks.ee/MsSql2000ExtendedDialect.cs

Server-side paging can be done reasonably efficiently in SQL 2000 using the following pattern:

SELECT * FROM table
WHERE primary key IN
(SELECT TOP page_size primary_key FROM table
WHERE primary_key NOT IN
(SELECT TOP page_size * (page_number - 1) primary_key FROM table
WHERE filter_conditions
ORDER BY sort_field)
AND filter_criteria
ORDER BY sort_field)
ORDER BY sort_field

Full details can be found here:

http://rosca.net/writing/articles/serverside_paging.asp

We have used this to great effect in our application and it would be nice if NHibernate had this built into the SQL dialect.

Jason

Urmo, Can you post an example of the paging in action with the datagrid if you have one handy.

thanks!

Unfortunately I don't have time to extract any examples. However, introduction to asp.net (quickstart) has probably one. So you have to take this bold step and explore it yourself.