OR-Combine two conditions using ICriteria

Question

I want to perform a simple selection with the conditions spread across 3 tables.

Select a.*
   FROM TBL_A a
   JOIN TBL_B b ON a.bId=b.Id
   JOIN TBL_C c ON a.cId=c.Id 
   JOIN TBL_D d ON c.dId=d.Id
WHERE (b.value='X' OR c.value='x')
   AND d.someFlag=1

Assuming that these tables are mapped correctly, how can such a query be achieved using ICriteria API?

What I have so far doesn't fulfill all of that...

Answer 1

The ICriteria query could look like this, following the documenation:

• 15.2. Narrowing the result set - how to use OR, we can take at the documentation:
• 15.4. Associations - how to join and alias tables

the query snippet:

 IList<A> list = sess.CreateCriteria(typeof(A), "a")
    .CreateAlias("B", "b")                  // B is property of A a.B
    .CreateCriteria("C", "c")               // C is property of A a.C
    .CreateAlias("D", "d")                  // D is property of C c.D

    .Add( Expression.Disjunction()          // OR statement
       .Add( Expression.Eq("b.value", "X" ) ) // a property "value" from "b" 
       .Add( Expression.Eq("c.value", "x" ) ) // a property "value" from "c"
    )
    .Add( Expression.Eq("d.someFlag", 1) )  // AND this expression

                                            // we are still selecting just a.*
    .SetResultTransformer(Transformers.AliasToBean<A>())
    .List<A>()