Distinct elements using NHibernate ICriteria for pagination

Question

I am implementing a pagination solution using NHibernate ICriteria where I wish to display distinct elements within the page.

The ICriteria for this is:

Session.CreateCriteria(typeof(Employee), "a")
            .CreateCriteria("Company", "b")
            //BUNCH OF JOINS
            .SetProjection(Projections.ProjectionList()
                     .Add(Projections.Distinct(
                          Projections.ProjectionList()
                         .Add(Projections.Property("a.Id"), "NameId")
                         .Add(Projections.Property("b.Id"), "CompanyId")
                         .Add(Projections.Property("a.Description"), "Description")
                         .Add(Projections.Property("b.Address"), "Address")    
                          )
                      )
                 )
            .SetResultTransformer(new  
             NHibernate.Transform.DistinctRootEntityResultTransformer())
            .SetResultTransformer(Transformers.AliasToBean<EntityData>())
            .SetFirstResult(80)
            .SetMaxResults(40)
            .List<EntityData>();

The query that gets generated is of the format:

   SELECT   TOP 40 y0_,
            y1_,
            y2_,
            y3_
    FROM     (SELECT distinct a.NameId        as y0_,
                      b.CompanyId             as y1_,
                      a.Description           as y2_,
                      b.Address               as y3_,
                      ROW_NUMBER()
                        OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row
      FROM   [EMPLOYEE] a
             //Bunch of inner joins and left outer joins
      ) as query
WHERE    query.__hibernate_sort_row > 80
ORDER BY query.__hibernate_sort_row)

Even though I have used DISTINCT as well as DistinctRootEntityResultTransformer, my results are not unique and I suspect that because of the ROW_Number being inserted in the result set, my query is no longer distinct and I end up with duplicate values.

Any pointers please?

Answer 1

Are you sure you aren't generating a cartesian product with all of your joins? That could explain why you aren't getting distinct results.