Search code examples
c#nhibernateleft-joinqueryover

How to limit rows count only of left part of join with QueryOver?

Take() limits the total rows count of the whole query result but I need to limit only rows of the left side of the join to retrieve 50 users and all their characters:

 session.QueryOver<User>(() => userAlias)
            .Where(...)
            //.Take(50) - can't do this! 
            .JoinAlias(x => x.Characters, () => characterAlias, JoinType.LeftOuterJoin)
            .Select(...)
            .TransformUsing(Transformers.AliasToBean<UserDto>())
            .Take(50)
            .List<UserDto>();

This query should be done through QueryOver.

Example sql: 

SELECT u.id, u.nickname, c.class_id, c.xp FROM (
   (SELECT id, nickname FROM users WHERE lower(nickname) LIKE 'abcd%' LIMIT 50) as u
   LEFT OUTER JOIN characters c ON c.user_id = u.id
)

Is it possible?

Solution

  • HQL doesn't support it. The only option here is to use pure sql query.