NHibernate criteria queries - How to chain logical operators

Question

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).

score 1 · Accepted Answer · answered Mar 20 '12 at 19:47

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...

Well in the end I didn't try this as my initial attempt actually worked and I don't feel like changing it. But I will mark this as the answer because it seems like an elegant solution (I hope it works too). I'll post my answer separately. — joniba, Mar 21 '12 at 09:32

score 1 · Answer 2 · edited May 23 '17 at 12:11

1

With the criteria API you can use the Conjunction (AND) and Disjunction (OR) classes. For an example, see this stackoverflow thread

edited May 23 '17 at 12:11

Community

1
1

answered Mar 20 '12 at 21:11

Dirk Trilsbeek

5,873
2
25
23

So after a Conjunction() you can add as many restrictions / expressions as you like and they will be joined by an add / or? Or did I not understand the example? – joniba Mar 21 '12 at 08:14
Basically yes. A conjunction takes a number restrictions and chains them with logical ANDs. A Disjunction also takes a number of restrictions but chains them with OR. You can also include a disjunction in a conjunction and vice versa. – Dirk Trilsbeek Mar 21 '12 at 09:30

score 0 · Answer 3 · answered Mar 21 '12 at 09:39

Well it seems my initial attempt actually worked, so I'll post up how I did it in case it interests anyone. It looks something like this:

    public IEnumerable<Entity> Filter(FilterRequest filterRequest)
    {
        var criteria = session.CreateCriteria("Entity");

        criteria.Add(
            Expression.And(
                CreateItemCriteria(filterRequest),
                CreateKeysCriteria(filterRequest)));

        return criteria.List<Entity>();
    }

    private static ICriterion CreateItemCriteria(FilterRequest filterRequest)
    {
        return Restrictions.Eq("a", filterRequest.ItemId);
    }

    private ICriterion CreateKeysCriteria(FilterRequest filterRequest)
    {
        ICriterion finalCriterion = null;

        for (int i = 0; i < filterRequest.Keys.Count; i++)
        {
            var currentKeyCriterion = CreateKeyCriterion(filterRequest.Keys[i]);

            finalCriterion = finalCriterion == null
                ? currentKeyCriterion
                : Expression.Or(finalCriterion, currentKeyCriterion);
        }

        return finalCriterion;
    }

    private ICriterion CreateKeyCriterion(Key key)
    {
        return Expression.AllEq(new Dictionary<string, object>
            {
                { "b", Key.b },
                { "c", Key.c },
                { "d", Key.d },
            });
    }

Not terribly elegant, but it works, and the result SQL is exactly as I wanted.

NHibernate criteria queries - How to chain logical operators

3 Answers3