NHibernate ManyToMany and eager loading: strange resultset for SetFetchmode combined with SetResultTransformer and SetMaxResult

Question

I've got a many-to-many relationship which I try to fetch eager:

*.CreateCriteria(typeof(Class1))
.SetFetchMode("Class2", FetchMode.Eager)
.SetResultTransformer(new DistinctRootEntityResultTransformer())
.SetFirstResult(20)
.SetMaxResult(10)
.List<Class1>();

I'd like to have rows 20-30 returned, but instead I've got 12-18. Why? Because the SetResultTransformer is executed AFTER the SetMaxResult. It returns 10 rows starting from row 20, but then it is distincted (is that a word?) resulting in rows 12-18. I totally understand what's happening, but can't think of a solution using criteria...

Does anyone have a solution to that?

Answer 1

I did not actually try this, but it might work.

  *.CreateCriteria(typeof(Class1))
    .SetFirstResult(20)
    .SetMaxResult(10)
    .CreateCriteria("Class2", JoinType.LeftOuterJoin)
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .List<Class1>();

EDIT: This does not work. Use multiquery, multicriteria (or Future when using a new build of NH)

Answer 2

I have the same problem. An adaptation of the following using detached criteria and subqueries looks like it might be promising. I'm going to do a more thorough trial of it when I get some time.

http://blogs.taiga.nl/martijn/2008/11/20/nhibernate-criteria-queries-across-multiple-many-many-associations/