Q: How to fetch with NHibernate a page from a couple of master – detail tables?
Let’s say we have a well known and simplified model of orders – order lines.
And we want to fetch a 3rd page (page size is 10) of the last orders received before specified day.
The intuitive solution is just query orders table with outer join on order lines table using standard paging technics.
But when we look at results we see that we fetched less than 10 orders! What was happened here?
Take a look on generated by NH sql:
It firstly joins orders with their order lines so we have multiple lines per same order and only than takes a specified page. So we not only fetching less than 10 orders, but we also not actually skipping 20 orders. We are skipping and taking joined rows.
As for me it’s a bug and not intended behavior.
I have expected that it firstly will take a needed page of orders and only than will join result with order lines.
So how anyway we can fetch a page of orders with their order lines in a most efficient way?
There is the best solution that I have found:
I use sub query to find a needed page of orders and than fetch orders with its order lines by order’s ids present in the sub query.
Pay attention. It does not work with 3.2 version of NH. In 3.2 version NH generates wrong SQL for Contains method. In our case it totally ignored sub select. The issue was fixed in the latest released version of NH: 3.3.1.4000.
Let’s say we have a well known and simplified model of orders – order lines.
And we want to fetch a 3rd page (page size is 10) of the last orders received before specified day.
The intuitive solution is just query orders table with outer join on order lines table using standard paging technics.
var res = session.Query<Order>() .Where(o => o.OrderTime < beforeDate) .OrderByDescending(o => o.OrderTime) .FetchMany(e => e.OrderLines) .Skip(20) .Take(10) .ToList();
But when we look at results we see that we fetched less than 10 orders! What was happened here?
Take a look on generated by NH sql:
SELECT TOP (@p0) Id4_0_, Id5_1_, Version4_0_, OrderTime4_0_, OrderId5_1_, ProductN3_5_1_, Price5_1_, Quantity5_1_, OrderId0__, Id0__ FROM ( select order0_.Id as Id4_0_, orderlines1_.Id as Id5_1_, order0_.Version as Version4_0_, order0_.OrderTime as OrderTime4_0_, orderlines1_.OrderId as OrderId5_1_, orderlines1_.ProductName as ProductN3_5_1_, orderlines1_.Price as Price5_1_, orderlines1_.Quantity as Quantity5_1_, orderlines1_.OrderId as OrderId0__, orderlines1_.Id as Id0__, ROW_NUMBER() OVER(ORDER BY order0_.OrderTime DESC) as __hibernate_sort_row from Orders order0_ left outer join OrderLines orderlines1_ on order0_.Id=orderlines1_.OrderId where order0_.OrderTime<@p1 ) as query WHERE query.__hibernate_sort_row > @p2
ORDER BY query.__hibernate_sort_row
It firstly joins orders with their order lines so we have multiple lines per same order and only than takes a specified page. So we not only fetching less than 10 orders, but we also not actually skipping 20 orders. We are skipping and taking joined rows.
As for me it’s a bug and not intended behavior.
I have expected that it firstly will take a needed page of orders and only than will join result with order lines.
So how anyway we can fetch a page of orders with their order lines in a most efficient way?
There is the best solution that I have found:
var subQuery = session.Query<Order>() .Where(o => o.OrderTime < beforeDate) .OrderByDescending(e => e.OrderTime) .Select(o => o.Id) .Skip(20) .Take(10); var res = session.Query<Order>() .FetchMany(e => e.OrderLines) .Where(e => subQuery.Contains(e.Id)) .OrderByDescending(o => o.OrderTime) .ToList();
I use sub query to find a needed page of orders and than fetch orders with its order lines by order’s ids present in the sub query.
select order0_.Id as Id4_0_, orderlines1_.Id as Id5_1_, order0_.Version as Version4_0_, order0_.OrderTime as OrderTime4_0_, orderlines1_.OrderId as OrderId5_1_, orderlines1_.ProductName as ProductN3_5_1_, orderlines1_.Price as Price5_1_, orderlines1_.Quantity as Quantity5_1_, orderlines1_.OrderId as OrderId0__, orderlines1_.Id as Id0__ from Orders order0_ left outer join OrderLines orderlines1_ on order0_.Id=orderlines1_.OrderId where order0_.Id in ( SELECT TOP (@p0) Id FROM ( select order2_.Id, ROW_NUMBER() OVER(ORDER BY order2_.OrderTime DESC) as __hibernate_sort_row from Orders order2_ where order2_.OrderTime<@p1 ) as query WHERE query.__hibernate_sort_row > @p2 ORDER BY query.__hibernate_sort_row ) order by order0_.OrderTime desc
Pay attention. It does not work with 3.2 version of NH. In 3.2 version NH generates wrong SQL for Contains method. In our case it totally ignored sub select. The issue was fixed in the latest released version of NH: 3.3.1.4000.
Thanks for this post. There are always headaches with the NHibernate ;-) I think more and more about moving to EF.
ReplyDelete