How to do pagination in an NHibernate SQL query with DISTINCT

I have a big and complicate SQL query with a DISTINCT clause that gets run in NHibernate (it's a search query with a wide variety of criteria and joins). But I'm having a problem using pagination with it under SQL Server 2008.

My question is: how do I do this from NHibernate and not my manual workaround (outlined below)?

The code I'm using is this:

 var searchQuery = BuildQuery(criteria);
     .SetTimeout(240)
     .SetFirstResult(pageIndex * pageSize)
     .SetMaxResults(pageSize);
 var resultRows = searchQuery.List<object[]>();

The results of my SQL query (from BuildQuery(), without DISTINCT) look like this (highly simplified):

Id, Name, SortColumn
1   AA    1/1/2000
1   AA    1/1/2000
2   AB    3/1/2000
2   AB    3/1/2000
3   AC    10/1/2000
3   AC    10/1/2000
....

I can't avoid the duplicate rows because of how the query works, so I stick a DISTINCT in the SQL query to make them go away.

To do the pagination, NHibernate generates a query like this (this example is from the second page with 100 results per page):

SELECT   TOP 100 Id,
             Name,
             SortColumn,
FROM     (select distinct Id, Name, SortColumn,
                 ROW_NUMBER() OVER (ORDER BY SortColumn) as __hibernate_sort_row
          from ......
          where ......) as query
WHERE    query.__hibernate_sort_row > 100
ORDER BY query.__hibernate_sort_row

This works fine for the first page, but on subsequent pages I only get half the expecte开发者_如何转开发d results.

I run the SQL inside NHibernate's subquery and found that the row NHibernate inserts to do it's pagination returns something unexpected (even with DISTINCT):

Id, Name, SortColumn,     __hibernate_sort_row
1   AA    1/1/2000,       1
1   AA    1/1/2000,       2
2   AB    3/1/2000,       3
2   AB    3/1/2000,       4
3   AC    10/1/2000,      5
3   AC    10/1/2000,      6
....

When NHibernate wraps this in a subquery to filter on the __hibernate_sort_row column, it returns 100 rows, but because every row is duplicate, I really only have 50 rows on my page. This is because DISTINCT isn't merging the rows as I expected any more. (All this comes back to how the ROW_NUMBER() function works in SQL 2008 with a DISTINCT clause, but it's NHibernate's problem, not mine.).

I've currently solved this problem by wrapping the query in two subqueries and manually handling the pagination:

SELECT TOP 100 paginationOuter.*
FROM
(
SELECT paginationInner.*
    ,ROW_NUMBER() OVER(ORDER BY SortColumn ASC) AS RowNum
FROM
    (
            select distinct Id, Name, SortColumn,
            from ......
            where ......
    ) AS paginationInner
) AS paginationOuter
WHERE    paginationOuter.RowNum > 100
ORDER BY paginationOuter.RowNum

My question is: how do I do this from NHibernate and not my manual workaround?

I'm using NHibernate version 2.1.2.

Unfortunately, I can't convert this query to HQL or LINQ (unless someone can supply me with a few weeks of free time!). Nor can I remove the DISTINCT from my query.

---

For anyone else who comes across this problem, seems like it is fixed in 3.3.1 See https://nhibernate.jira.com/browse/NH-2214