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 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?

Answers


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.


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


Need Your Help

Add and Remove Views in Android Dynamically?

android android-view

How do I add and remove views such as TextViews from Android app like on the original stock Android contacts screen where you press a small icon on the right side of a field and it adds or deletes a

How to make image layer visible in openlayers

javascript visibility openlayers openstreetmap

I am using OpenLayers with OpenStreetMaps to develop a small GIS-application.