Multiple AND/OR Statements with NHibernate

I’m sure you’ve encountered the scenario where you need to add multiple AND or OR statements to an NHibernate query.  The string parsing and manipulation required to do it in HQL would be enough to send you straight to programmer hell.  So it turns out the easiest (in my opinion) option is to use the good ol’ Criteria API.  But it’s not as obvious as it first seems.  In order to accomplish the task, you actually need to use a pair of constructs that may be new to you: Conjunction which represents a grouping of AND statements (A and B and C…); and Disjunction, a grouping of OR statements (A or B or C…).  Here’s an admittedly contrived example of how you could query for Persons by FirstName that are not archived (IsArchived), ordering by FirstName:

Contrived Disjunction Example

1. string[] namesToCheck = { "Nick", "Dave", "Bryan", "James" };
2. using (ISession session = SessionManager.CreateSession())
3. {
4.     var criteria = session.CreateCriteria<Person>();
5.     var disjunction = Restrictions.Disjunction();
6.  
7.     criteria.Add(Expression.Eq("IsArchived", false));
8.  
9.     foreach (var name in namesToCheck)
10.     {
11.         disjunction.Add(Restrictions.Eq("FirstName", name));
12.     }
13.  
14.     criteria.Add(disjunction);
15.     criteria.AddOrder(Order.Asc("FirstName"));
16.  
17.     return criteria.List<Person>();
18. }

This would generate something similar to the following SQL-statement:

Disjunction SQL Results

1. SELECT * FROM Person
2. WHERE IsArchived = 0 AND
3.     (FirstName = "Nick" OR
4.     FirstName = "Dave" OR
5.     FirstName = "Bryan" OR
6.     FirstName = "James")
7. ORDER BY FirstName

If you needed ANDs instead of ORs you would use Conjunction instead of Disjunction.

A more powerful example would be if you needed multiple sets of statements.  Consider the following scenario:

Select all Persons where IsArchived is false and the FirstName is “Nick”, or all Persons where IsArchived is true and the FirstName is “Bryan”

Here’s how you would do it using Conjunctions and Disjunctions:

Less Contrived Example

1. using (ISession session = SessionManager.CreateSession())
2. {
3.     var criteria = session.CreateCriteria<Person>()
4.         .AddOrder(Order.Asc("FirstName"));
5.  
6.     // IsArchived is FALSE and FirstName = "Nick"
7.     var conjunction1 = Restrictions.Conjunction();
8.     conjunction1.Add(Restrictions.Eq("IsArchived", false));
9.     conjunction1.Add(Restrictions.Eq("FirstName", "Nick"));
10.  
11.     // IsArchived is TRUE and FirstName = "Bryan"
12.     var conjunction2 = Restrictions.Conjunction();
13.     conjunction2.Add(Restrictions.Eq("IsArchived", true));
14.     conjunction2.Add(Restrictions.Eq("FirstName", "Bryan"));
15.  
16.     // This combines the two statements into an OR
17.     var disjunction = Restrictions.Disjunction();
18.     disjunction.Add(conjunction1);
19.     disjunction.Add(conjunction2);
20.  
21.     criteria.Add(disjunction);
22.  
23.     return criteria.List<Person>();
24. }

Which would produce the following:

SQL Results

1. SELECT * FROM Person
2. WHERE
3.     (IsArchived = 0 AND FirstName = "Nick") OR
4.     (IsArchived = 1 AND FirstName = "Bryan")
5. ORDER BY FirstName