NHibernate DetachedCriteria - how to use something like Oracle's NVL on outer-joined set

Question

I'd like to create DetachedCriteria which would perform something similar to the following SQL code:

select *
FROM PRICELIST pl
LEFT OUTER JOIN PRICELISTDURATIONSHIFT sh ON sh.PRICELISTID = pl.ID
WHERE sh.CUSTOMERID = :cust
  AND nvl(sh.DATEFROM, pl.DATEFROM) <= :dt
ORDER BY nvl(sh.DATEFROM, pl.DATEFROM) DESC

I'm able to left-outer-join the PriceListDurationShift class/table and add the Customer constraint, but I'm not able to figure out how to add something like the nvl(sh.DATEFROM, pl.DATEFROM) <= :dt restriction.

I'd appreciate any advice for this sample and also any advice on NHibernate materials on advanced DetachedCriteria querying.

---

hopefully this would work:

var priceLists = priceListRepo.FindAll(DetachedCriteria.For<PriceList>("pl")
    .CreateCriteria((PriceList x) => x.PriceListDurationShifts, () => shift,
        JoinType.LeftOuterJoin)
    .Add<PriceListDurationShift>(x => x.Customer == cust)
    .Add(Expression.Or(
        Expression.And(Restrictions.IsNull("shift.DateFrom"),
            Restrictions.Le("pl.DateFrom", dt.DateTo)),
        Expression.And(Restrictions.IsNotNull("shift.DateFrom"),
            Restrictions.Le("shift.DateFrom", dt.DateTo))
        ))
    )
    .ToList();

Answer 1

I've looked for something like this as well, but haven't found a solution to it.

In the meantime, I've solved this by using HQL instead of the Criteria API.

HQL has a method coalesce which can be used for this purpose.

from PriceList as pl
left join pl.PriceListDurationShift as shift
where coalesce (shift.DateFrom, pl.DateFrom) <= :dt
order by coalesce (shift.DateFrom, pl.DateFrom)