Search code examples
nhibernatenhibernate-criteria

NHibernate criteria queries - How to chain logical operators

I'm looking for an example of how to create a criteria query that would result SQL similar to this (or with an equivalent effect):

SELECT x, y, z
FROM SomeTable tbl
WHERE tbl.a = 'some value' 
  AND (
    (tbl.b = '1' AND tbl.c = 'whatever1' AND tbl.d = 123) OR
    (tbl.b = '2' AND tbl.c = 'whatever2' AND tbl.d = 456) OR
    (tbl.b = '3' AND tbl.c = 'whatever3' AND tbl.d = 789)
  )

When creating the query I have a list of filter data (which fills the data that comes after the "AND") as well as an extra parameter (which fills the 'some value' portion above).

Basically my question is how do I chain ANDs and ORs when building this kind of criteria query? The API for Expression.And and Expression.Or only accept a single left and right criterion, not a chain.

Does anyone know where I can find an example for this?

BTW, the x,y,z part (after the SELECT) is currently irrelevant as it seems I can accomplish it with projection (haven't gotten there yet).

Solution

  • There is no such thing as logical operator chaining. The above can also be written as

    (tbl.b = '1' AND tbl.c = 'whatever1' AND tbl.d = 123) OR
    ((tbl.b = '2' AND tbl.c = 'whatever2' AND tbl.d = 456) OR
     (tbl.b = '3' AND tbl.c = 'whatever3' AND tbl.d = 789))

    That is, logic operators ALWAYS have a left and a right parameter.

    That said, the bitwise operators are overloaded for Restriction, so the following works:

    criteria.Add(Restrictions.Eq("a", "some value") &
             (Restrictions.Eq("b", 1) & Restrictions.Eq("c", "whatever1") |
             (Restrictions.Eq("b", 2) & Restrictions.Eq("c", "whatever2"))))
             //...etc...