Tuesday, December 14, 2010

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

6 comments:

  1. With NHibernate 3 we have full Linq support. Combine that with the specification implementation of http://code.google.com/p/ef4prs/downloads/list and you will be able to write this:


    // IsArchived is FALSE and FirstName = "Nick"
    var spec1 = new Specification(p => !p.IsArchived && p.FirstName == "Nick");

    // IsArchived is TRUE and FirstName = "Bryan"
    var spec2 = new Specification(p => p.IsArchived && p.FirstName == "Bryan");

    // This combines the two statements into an OR
    var combined = spec1.Or(spec2);

    return combined.SatisfyingEntitiesFrom(session.Query());

    Nice huh?

    ReplyDelete
  2. That's much cleaner! I updated all of my statements to use the new syntax after upgrading to NHibernate v3. Thanks for the example, Danne!

    ReplyDelete
  3. Thanks for this, save me a bunch of time :)

    ReplyDelete
  4. excellent. Thats what I was looking for Nick!

    ReplyDelete