Geek Answers Handbook

How to do pagination in NHibernate SQL query with DISTINCT

I have a large and complex SQL query with a DISTINCT clause that runs in NHibernate (this is a search query with a wide range of criteria and associations). But I have a problem using pagination with it under SQL Server 2008.

My question is: how do I do this with NHibernate, and not with my workaround (see below)?

The code I use is as follows:

 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 (very 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 cannot avoid duplicating rows because of how the query works, so I insert DISTINCT in the SQL query to make them leave.

To paginate, NHibernate generates such a query (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

, .

SQL NHibernate , NHibernate - ( 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
....

NHibernate __hibernate_sort_row, 100 , , 50 . , DISTINCT , . ( , ROW_NUMBER() SQL 2008 DISTINCT, NHibernate, .).

, :

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

: NHibernate, ?

NHibernate 2.1.2.

, HQL LINQ ( - !). DISTINCT .

+5

More articles:


All Articles