nHibernate projection - paging and distinct

Question

The high level problem I am trying to solve is de-duplicated paging when searching for things that may exist in child collections.

The approach I'm taking is by creating a distinct Projection, containing the information pertinent to my DTO. I also know these values don't change between each row.

criteria.SetProjection(
  Projections.Distinct(Projections.ProjectionList()
    .Add(Projections.Alias(Projections.Property("Id"), "Id"))
    .Add(Projections.Alias(Projections.Property("Title"), "Title"))
    .Add(Projections.Alias(Projections.Property("Firstname"), "Firstname"))
    .Add(Projections.Alias(Projections.Property("Surname"), "Surname"))
    .Add(Projections.Alias(Projections.Property("DateCreated"), "DateCreated"))));

The problem occurs when I introduce the paging / ordering elements.

Looking at NH Profiler, I'm seeing the SQL like:-

SELECT TOP (20 /* @p0 */) y0_,
  y1_,
  y2_,
  y3_,
  y4_
FROM   
(
  SELECT distinct 
    this_.Id as y0_,
    this_.Title as y1_,
    this_.Firstname as y2_,
    this_.Surname as y3_,
    this_.DateCreated as y4_,
    ROW_NUMBER() 
      OVER(ORDER BY this_.Firstname DESC, this_.Surname DESC) 
     as __hibernate_sort_row
  FROM   Users this_
) as query
WHERE  query.__hibernate_sort_row > 20 /* @p1 */
ORDER  BY query.__hibernate_sort_row;

My intention, to get a DISTINCT projection, is being thwarted because ROW_NUMBER() is making every row unique.

I have come across this blog post, which describes the same problem I am experiencing.

http://www.daczkowski.net/2010/09/07/rows-duplication-for-certain-nhibernate-queries-%E2%80%93-workaround/

The solution offered up there involved changing the nHibernate MS SQL code; which is an option, but one I am keen to avoid. Are there options in more recent versions of nHibernate which get around this problem?

EDIT

Fixed in nHibernate 3.3.1 - see answer below.

Answer 1

To remove these duplicates, I tend to set the fetch mode to "Select" or "Lazy" for collections properties when doing paging (this way, disabling mapping's "Eager" or "Join" fetch mode )

Assuming you have a property Children, this would lead to removing the projections and adding :

criteria.SetFetchMode("Children", FetchMode.Select);

and

criteria.SetResultTransformer(NHibernate.Transform.Transformers.DistinctRootEntity).List();

Answer 2

Following a bit of further research, I have now found a solution that I'm happy with.

According to this:-

https://nhibernate.jira.com/browse/NH-2492

The bug was addressed in nHibernate 3.3.1.CR1.

I've just downloaded nHibernate 3.3.1 stable, and have successfully used the same strategy without duplication.

For those that are interested, nh 3.3.1 spits out the following SQL.

SELECT distinct TOP (20 /* @p0 */) this_.Id as y0_,
  this_.Title as y1_,
  this_.Firstname       as y2_,
  this_.Surname     as y3_,
  this_.DateCreated  as y4_,
 FROM   Users this_
ORDER  BY this_.Firstname desc,
          this_.Surname desc;