NHibernate - generating WHERE IN … OR … queries

Let’s assume that we have a simple table GL_Task which looks like this

I was asked to rewrite simple SQL query

SELECT Id,Name,IdProject FROM GL_Task WHERE Name = 'First task' OR Id IN (3,4 /*more id's to come*/)

using NHibernate’s QueryOver API. As simple as it may seem, solution for this particular problem is not straightforward. My first (not so clever) attempt was simply combining WhereRestrictionOn and Where clause

1
var result = session.QueryOver<Task>().Where(val=> val.Name == "First task")
2
                                      .WhereRestrictionOn(val=> val.Id)
3
                                      .IsIn(new []{3,4})
4
                                      .List();

Of course this query returns wrong data because of the fact, that NHiberante by default “joins” queries using AND operator. So above query will result in generating something like that

SELECT Id,Name,IdProject FROM GL_Task WHERE Name = 'First task' AND Id IN (3,4 /*more id's to come*/)

After a little digging I managed to write appropriate query

1
var orRestriction = Restrictions.Or(
2
                         Restrictions.Where<Task>(val => val.Name == "First task"),
3
                         Restrictions.On<Task>(val => val.Id).IsIn(new[] { 3,4 }));
4

5
var result = session.QueryOver<Task>()
6
                    .Where(orRestriction)
7
                    .List();

Although this code generates correct SQL, it is hard to use it in case of many OR statements. For example, adding one more OR force us to write

1
var orRestriction = Restrictions.Or(
2
      Restrictions.Or(
3
            Restrictions.Where<Task>(val => val.Name == "First task"),
4
            Restrictions.On<Task>(val => val.Id).IsIn(new[] {3, 4})
5
       ),
6
       Restrictions.On<Task>(val => val.Name).IsIn(new[] {"FirstTask", "SecondTask"})
7
);
8

9
var result = session.QueryOver<Task>()
10
                    .Where(orRestriction)
11
                    .List();

In my opinion this is a little bit hard to read, so I continued my searching to find more elegant solution. My third attempt was based on Restrictions.Disjunction

1
var disjunctinos = Restrictions.Disjunction()
2
                           .Add(Restrictions.Where<Task>(val => val.Name == "First task"))
3
                           .Add(Restrictions.On<Task>(val => val.Id).IsIn(new[] { 3,4 }));
4

5
var result = session.QueryOver<Task>()
6
                    .Where(disjunctinos)
7
                    .List();

This was almost perfect solution. Adding new OR statements do not require us to do function nesting

1
var disjunctinos = Restrictions.Disjunction()
2
                          .Add(Restrictions.Where<Task>(val => val.Name == "First task"))
3
                          .Add(Restrictions.On<Task>(val => val.Id).IsIn(new[] { 3,4 }))
4
                          .Add(Restrictions.On<Task>(val => val.Name).IsIn(new[] { "FirstTask", "SecondTask" }));
5

6
var result = session.QueryOver<Task>()
7
                    .Where(disjunctinos)
8
                    .List();

However the ultimate solution is to just add NHibernate.Criterion namespace and use IsIn extension method. This operation simplify our query into single lambda expression

1
var result = session.QueryOver<Task>()
2
                    .Where(val => val.Name == "First task" || val.Id.IsIn(new[] { 3,4 }))
3
                    .List();

Source code for this post can be found here