Wednesday, April 25, 2012

Extending QueryOver With "Or"

QueryOver (introduced in NHibernate 3.0) offers type safe, Linq-esque, syntax for writing Nhibernate queries in your DAO. However, writing a multiple column disjunction with anything other than simple operators can easily become ugly and unwieldy.

With QueryOver, we can do awesome things like:
var bk = QueryOver.Of<Book>();
bk.Where(b => b.Name == "Of Mice And Men");
Which is miles better than the ICriteria equivalent:
var bk = DetachedCriteria.For<Book>();
bk.Add(Restrictions.Eq("Name", "Of Mice And Men"));
QueryOver even allows search operators that do not translate directly to C# operators. "LIKE" for instance:
bk.WhereRestrictionOn(b => b.Name).IsInsensitiveLike("Of Mice");
Which can also be written:
bk.Where(Restrictions.On<Book>(b => b.Name).IsInsensitiveLike("Of Mice"));
Allowing us to create a simple disjunction:
bk.Where(Restrictions.Or(
    Restrictions.On<Book>(b => b.Name).IsInsensitiveLike(search),
    Restrictions.On<Book>(b => b.Summary).IsInsensitiveLike(search)
));
So far, so elegant.

The trouble starts when you need to create a disjunction using more than two "On" restrictions. Because "Or()" only accepts two arguments, things will rapidly spiral out of control if you try to extend this approach. Just look what it turns into with 5 fields.
bk.Where(Restrictions.Or(
    Restrictions.On<Book>(b => b.Name).IsInsensitiveLike(search),
    Restrictions.Or(
        Restrictions.On<Book>(b => b.Summary).IsInsensitiveLike(search),
        Restrictions.Or(
            Restrictions.On<Book>(b => b.Synopsis).IsInsensitiveLike(search),
            Restrictions.Or(
                Restrictions.On<Book>(b => b.BookCode).IsInsensitiveLike(search),
                Restrictions.On<Book>(b => b.ISBN).IsInsensitiveLike(search)
            )
        )
    ) 
));
This makes me very sad.

But there's hope! Thanks to C#'s extension feature and param arguments, we can write a method that will take as many abstract criteria as we care to hand it. Here's what I came up with:
public static class Extensions
{
    public static IQueryOver<troot, tsubtype> Or<troot, tsubtype>(this IQueryOver<troot, tsubtype> input, params ICriterion[] criteria)
    {
        if (criteria.Length == 0)
            return input;
        else if (criteria.Length == 1)
            return input.Where(criteria[0]);
        else
        {
            var or = Restrictions.Or(criteria[0], criteria[1]);
            for (int i = 2; i < criteria.Length; i++)
                or = Restrictions.Or(or, criteria[i]);
 
           return input.Where(or);
        }
    }
}
And now our crazy where clause simplifies to this:
var bk = QueryOver.Of<Book>();
bk.Or(
    Restrictions.On<Book>(b => b.Name).IsInsensitiveLike(search),
    Restrictions.On<Book>(b => b.Summary).IsInsensitiveLike(search),
    Restrictions.On<Book>(b => b.Synopsis).IsInsensitiveLike(search),
    Restrictions.On<Book>(b => b.BookCode).IsInsensitiveLike(search),
    Restrictions.On<Book>(b => b.ISBN).IsInsensitiveLike(search)
);

No comments: