How to implement paging in NHibernate with a left join query

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 fo开发者_Python百科r 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?

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

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.