Left join, distinct and paging with QueryOver

I have such problem which seems to be common, but in fact none of solutions from SO and google work for me.

Lets consider such situation (very similiar to mine):

    public class User
    {
     public int Id;
     public string Name;
     //30 other fields;
     public IList<Role> Roles;
    }

    public class Role
    {
     public int RoleNumber;
     public int UserId;
    }

1 User can have 0-many roles.

I want to display on gridview list of users with its fields and additional "Roles" column which will display all his RoleNumbers (using repeater). To obtain that, I need to get distincted list of Users with its Roles.

Is it possible to create such query with QueryOver ? All the solutions from internet say that I should create subquery which will Ids and then second query which will return Users which Id is in subquery. It works and returns me distincted list, but without Roles, so NH generates additional select for each user to get that missing information.

My Query:

Role roles = null;
User users = null;
IQueryOver<User,User> userQuery = ... 
IQueryOver<User,User> distQuery = ...
distQuery.JoinAlias(f=> f.Roles, () => Roles, JoinType.LeftOuterJoin)
         .Select(Projections.Distinct(Projections.Id()));

userQuery.WithSubquery.WhereProperty(g => g.Id).In<User>((QueryOver<User>)    distQuery) //and other joins, wheres etc.

returnes distincted list without Roles

If I add 2nd Join to Roles it will return list with Roles and duplicates

If I add 2nd Join to Roles and use

TransformUsing(new DistinctRootEntityResultTransformer())

It will return distincted list with Users and it roles but paging won't work and RowCount() will return incorrect number.

Any ideas what can I do ?

Basically what you are asking for is not currently supported with NHibernate. You cannot eager load Users and Roles and keep pagination in check.

I would first get a paged list of users (e.g. 25) then lazy load the roles as and when you need them.

Session.QueryOver<User>().Skip(x).Take(25);

and change your mapping file to:-

<bag class="Role" Name="Roles" batch-size='25' ...>
...
</bag>

This would send one query to retrieve a list of paged Users then issue a second query to get all roles for the users. This is by far the most performant way.