Creating a nested OR statement using NHibernate Criteria API

Question

So I love learning new things about NHibernate because it truly is very flexible and makes my life easy except for where i'm stuck at right now ;) . So far I haven't had to do very complex queries, and this one isn't complex either, but I feel that the most commonly used methods and object types won't cover this problem.

Here is what I want to query:

SELECT _Table1.UserID, _Table1.ColorID, _Table1.AppID FROM _Table1
INNER JOIN _Table2 ON _Table2.AppID = _Table1.AppID 
WHERE _Table1.ColorID= @param0
AND (
      _Table2.WindowID = @param1 OR
      _Table2.WindowID = @param2 OR
      _Table2.WindowID = @param3 OR
      ................ = @param(N - 1)
    )

and here is what I have using the criteria API

private IList<Table1Entity> FetchListByWindowId(int colorID, List<int> listOfWindowIDs)
{

    var list = CurrentSession.CreateCriteria<Table1Entity>()
                 .Add(Restrictions.Eq("ColorID", colorID))
                 .CreateCriteria("Table2EntityList");

    foreach (var item in listOfWindowIDs)
    {
        list.GetCriteriaByPath("Table2EntityList")
            .Add(Restrictions.Eq("WindowID", Int32.Parse(item)
    }
}

The mapping for table one includes 4 properties UserID, ColorID, AppID, and a HasMany to the Table2 entity.

HasMany<Table2Entity>(x => x.Table2EntityList)
     .KeyColumns.Add("AppID")
     .Not.LazyLoad()
     .Inverse()
     .Cascade.None();

From the full day I've put into searching for a solution, I don't think there is an easy way to explicitly say "Make these expressions OR" (yes I have looked at Expression.OR and that's not what I want).

The query NHibernate is giving me is exactly what I want except that I need to replace some "ANDs" with "ORs"

Thanks, and sorry if this turns out to be very trivial. I also want to apologize for the vagueness of my code, but I deal with sensitive data. So all variable names and method names and the like had to be adjusted.

EDIT: The query that NHibernate is currently giving me:

SELECT _Table1.UserID, _Table1.ColorID, _Table1.AppID FROM _Table1
INNER JOIN _Table2 ON _Table2.AppID = _Table1.AppID 
WHERE _Table1.ColorID= @param0
AND (
      _Table2.WindowID = @param1
      AND _Table2.WindowID = @param2 
      AND _Table2.WindowID = @param3
      AND ................ = @param(N - 1)
    )

Possible duplicate: http://stackoverflow.com/questions/434138/how-to-set-more-than-2-expression-in-expression-or — Gert Arnold, Feb 15 '12 at 20:14
No I've looked that and all I could get is Expression.Or wants two things to compare against each other. I believe what I want is to restrict a field to be equal to an item in a set of data. I'm not doing a comparison. But I haven't spent time looking at Disjunction. So I'll go look at that. — CSMHowitzer, Feb 15 '12 at 20:34
I think Disjunction() was a step in the right path since I have a set of items and I want to say something like A or B or C... But I added an Expressions.Disjunction() and I still ended up with the same query. — CSMHowitzer, Feb 15 '12 at 20:50

score 5 · Accepted Answer · answered Feb 15 '12 at 21:46

5

Something like:

var disjunction = new Disjunction()
    .Add(Restriction.Eq("WindowID", item1))
    .Add(Restriction.Eq("WindowID", item2))
    .Add(Restriction.Eq("WindowID", item3));
// Or use a loop if you like...

var list = CurrentSession.CreateCriteria<Table1Entity>()
    .Add(Restrictions.Eq("ColorID", colorID))
    .CreateCriteria("Table2EntityList")
    .Add(disjunction);

answered Feb 15 '12 at 21:46

Iridium

23,323
6
52
74

Yes, this worked. Awesome, I really appreciate your help. What I did was I created a disjunction and in my loop I added restrictions to it and then I made my list (just like your answer really). The query came out as "SELECT * FROM TABLE1 INNER JOIN TABLE2 ON ID = ID WHERE COLOR = P0 AND (Window = P1 OR P2....PN)" Thanks for the quick reply. – CSMHowitzer Feb 16 '12 at 13:45

Creating a nested OR statement using NHibernate Criteria API

1 Answers1