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.
