CODEWITHSUNDEEP
nhibernatenhibernate-criteria
joniba
joniba

Reputation: 3499

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

Upvotes: 0

Views: 1741

Answers (3)

joniba
joniba

Reputation: 3499

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.

Upvotes: 0

Dirk Trilsbeek
Dirk Trilsbeek

Reputation: 6023

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

Upvotes: 1

Diego Mijelshon
Diego Mijelshon

Reputation: 52745

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

Upvotes: 1

Related Questions