NHibernate Pitfalls: Fetch and Paging

This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.

NHibernate allows you to force loading additional references (many to one, one to one) or collections (one to many, many to many) in a query. You must know, however, that this is incompatible with paging. It’s easy to see why.

Let’s say you want to get 5 products starting on the fifth, you can issue the following LINQ query:

   1: session.Query<Product>().Take(5).Skip(5).ToList();

Will product this SQL in SQL Server:

   1: SELECT
   2:     TOP (@p0) product1_4_,
   3:     name4_,
   4:     price4_
   5: FROM
   6:     (select
   7:         product0_.product_id as product1_4_,
   8:         product0_.name as name4_,
   9:         product0_.price as price4_,        
  10:         ROW_NUMBER() OVER(
  11:     ORDER BY
  12:         CURRENT_TIMESTAMP) as __hibernate_sort_row
  13:     from
  14:         product product0_) as query
  15:     WHERE
  16:         query.__hibernate_sort_row > @p1
  17:     ORDER BY

If, however, you wanted to bring as well the associated order details, you might be tempted to try this:

   1: session.Query<Product>().Fetch(x => x.OrderDetails).Take(5).Skip(5).ToList();

Which, in turn, will produce this SQL:

   1: SELECT
   2:     TOP (@p0) product1_4_0_,
   3:     order1_3_1_,
   4:     name4_0_,
   5:     price4_0_,
   6:     order2_3_1_,
   7:     product3_3_1_,
   8:     quantity3_1_,
   9:     product3_0__,
  10:     order1_0__
  11: FROM
  12:     (select
  13:         product0_.product_id as product1_4_0_,
  14:         orderdetai1_.order_detail_id as order1_3_1_,
  15:         product0_.name as name4_0_,
  16:         product0_.price as price4_0_,
  17:         orderdetai1_.order_id as order2_3_1_,
  18:         orderdetai1_.product_id as product3_3_1_,
  19:         orderdetai1_.quantity as quantity3_1_,
  20:         orderdetai1_.product_id as product3_0__,
  21:         orderdetai1_.order_detail_id as order1_0__,
  22:         ROW_NUMBER() OVER(
  23:     ORDER BY
  24:         CURRENT_TIMESTAMP) as __hibernate_sort_row
  25:     from
  26:         product product0_
  27:     left outer join
  28:         order_detail orderdetai1_
  29:             on product0_.product_id=orderdetai1_.product_id
  30:         ) as query
  31: WHERE
  32:     query.__hibernate_sort_row > @p1
  33: ORDER BY
  34:     query.__hibernate_sort_row;

However, because of the JOIN, what happens is that, if your products have more than one order details, you will get several records – one per order detail – per product, which means that pagination will be broken.

There is an workaround, which forces you to write your LINQ query in another way:

   1: session.Query<OrderDetail>().Where(x => session.Query<Product>().Select(y => y.ProductId).Take(5).Skip(5).Contains(x.Product.ProductId)).Select(x => x.Product).ToList()

Or, using HQL:

   1: session.CreateQuery("select od.Product from OrderDetail od where od.Product.ProductId in (select p.ProductId from Product p skip 5 take 5)").List<Product>();

The generated SQL will then be:

   1: select
   2:     product1_.product_id as product1_4_,
   3:     product1_.name as name4_,
   4:     product1_.price as price4_
   5: from
   6:     order_detail orderdetai0_
   7: left outer join
   8:     product product1_
   9:         on orderdetai0_.product_id=product1_.product_id
  10: where
  11:     orderdetai0_.product_id in (
  12:         SELECT
  13:             TOP (@p0) product_id
  14:         FROM
  15:             (select
  16:                 product2_.product_id,
  17:                 ROW_NUMBER() OVER(
  18:             ORDER BY
  19:                 CURRENT_TIMESTAMP) as __hibernate_sort_row
  20:             from
  21:                 product product2_) as query
  22:         WHERE
  23:             query.__hibernate_sort_row > @p1
  24:         ORDER BY
  25:             query.__hibernate_sort_row);

Which will get you what you want: for 5 products, all of their order details.

                             

No Comments