DistinctRootEntityTransformer with SetMaxResults or N + 1 SELECT

I've already posted this question to nh group but still didn't get t开发者_开发知识库he answer so I'm posting it here. I have a little problem with eager loading of child collection and getting a correct result with paging using SetMaxresults.

Let's say I have a simple entities:

 public class Post
{
                int Id {get; set;}
                Vote Votes {get; set;}
}

public class Vote
{
                int Id {get; set;}
}

At the beginning I had a Votes collection lazy loaded and it worked well except for the N+1 SELECT statements.

Then I tried to eagerly fetch 'Votes' collection which worked well but on the database side there were duplicates (if one Post have 3 Votes, we will have 3 rows in the resulting SQL query) I used DistinctRootEntityTransformer which also worked well except with SetMaxResults. Because the SetMaxResults limits the results on the database side we will have less results then expected on the object side after applying DistinctRootEntityTransformer.

I looked through blog posts, StackOverflow questions, tried many things like Fetch, LeftOuterJoins, Subqueries, detached criteria but didn't find any solution for my issue. The issue is also described here http://www.interworks.com/blogs/banderton/2009/06/26/nhibernate-eager-loading-collections-rootentityresulttransformer-and-setm.

For now I see three solutions, get incorrect result number with setMaxResults, keep SELECT N + 1, or set batch-size on lazy loaded 'vote' collection and that's what I did. The problem of the latest is that NHProf yields 'unbounded result set' alert for batched select, because it's not optimal.

Maybe I'm missing something. Is there any solution to solve it properly ?

Thanks in advance,

I would keep lazy loading and try adding batch size to your mapping to ease the select n+1 problem.

<bag name="Votes" cascade="all" batch-size="20">
..
</bag>

Basically setting batch-size to 20 will reduce the amount of queries issued to the database by a a factor of 20. Without it set if you had 99 rows you would issue 99 queries to the databse with batch-size set you would issue 5.

Eager joining produces a Cartesian join and therefore paging will not be correct. Also be aware that the DistinctRootEntity only provides client side filtering of data NOT actually on the database itself.