Subqueries in nHibernate and testing result for NULL

• Posted by Jakob Andersen on March 10, 2008
• 0 comments

If you haven't noticed yet i like working with nHibernate, however once in a while you hit something not implemented in nHibernate (yet!).

A while ago i worked on a project using nHibernate and should construct a query, this included a subselect which shouldn't be a problem, nHibernate has great support for many complex cases of querying.

To visualize the problem i stumbled upon during the creation of this query, think about an Account with a number of Deposits associated. We want to retrieve all Accounts which have a balance below a certain amount. Sorry for the example, this could be done in a number of ways to avoid using subqueries, but im not very good at making up examples and the specific domain is a little complex.

Anyways here is some sample code:

public void     TestIfAccountIsBelowLimit()
{
    using(ISession s = OpenSession())
    {
        using(ITransaction tx = s.BeginTransaction())
        {
            Account account1 = new Account("Account 1");
            account1.Deposits.Add(new Deposit(10));
            account1.Deposits.Add(new Deposit(20));
            Account account2 = new Account("Account 2");
            account2.Deposits.Add(new Deposit(40));
            s.Save(account1);
            s.Save(account2);
            tx.Commit();
        }
    }

    using(ISession s = OpenSession())
    {
        //Set up a criteria query to get the sum of all deposits
        DetachedCriteria subquery = DetachedCriteria.For()
            .SetProjection(Projections.Sum("Amount"))
            .Add(Expression.EqProperty("a.id", "Account.id"));

        //USe subquery to fint all accounts with less than 35 in total deposits
        IList accounts = s.CreateCriteria(typeof (Account), "a")
            .Add(Subqueries.Lt(35, subquery))
            .List();

        Assert.AreEqual(1, accounts.Count);
    }
}

But if i add another account to the test, without any deposits, the SUM would return NULL and hence not be less than 35 (Again sorry for the example this could be solved using exists). But to get to the point, nHibernate has no way of checking if the result of a subquery is null as far as i have figured out, so the SQL expressed like this:

SELECT * FROM Foo WHERE (SELECT SUM(x) FROM Bar WHERE ...) IS NULL

Can't be expressed using nHibernate. So the result of my specific case ended with using a custom SQLProjection(Where i specify SQL that is included directly in the generated SQL):

criteria.SetProjection(Projections.SqlProjection("COALESCE(SUM(column),0)"));

It works but it isn't pretty so i hope that my patch for adding Subqueries.IsNull() will be applied some day to the nHibernate source :-)