How to implement paging in NHibernate with a left join query

Question

I have an NHibernate query that looks like this:

        var query = Session.CreateQuery(@"
              select o
              from Order o
                left join o.Products p
              where
                (o.CompanyId = :companyId) AND
                (p.Status = :processing)
              order by o.UpdatedOn desc")
              .SetParameter("companyId", companyId)
              .SetParameter("processing", Status.Processing)
              .SetResultTransformer(Transformers.DistinctRootEntity);

        var data = query.List<Order>();

I want to implement paging for this query, so I only return x rows instead of the entire result set.

I know about SetMaxResults() and SetFirstResult(), but because of the left join and DistinctRootEntity, that could return less than x Orders.

I tried "select distinct o" as well, but the sql that is generated for that (using the sqlserver 2008 dialect) seems to ignore the distinct for pages after the first one (I think this is the problem).

What is the best way to accomplish this?

Answer 1

Use SetResultTransformer(Transformers.AliasToBean()) and get the data that is not the entity.

The other solution is that you change the query. As I see you're returning Orders that have products which are processing. So you could use exists statement. Check nhibernate manual at 13.11. Subqueries.

Answer 2

In these cases, it's best to do it in two queries instead of one:

1. Load a page of orders, without joins
2. Load those orders with their products, using the in operator

There's a slightly more complex example at http://ayende.com/Blog/archive/2010/01/16/eagerly-loading-entity-associations-efficiently-with-nhibernate.aspx