Thursday, November 1, 2012

NHibernate, Paging and eager loading

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.
Model
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.

1 comment:

  1. Thanks for this post. There are always headaches with the NHibernate ;-) I think more and more about moving to EF.

    ReplyDelete