Using a "in set" query with Nhibernate

I had a problem using the  Expression,In object in NHibernate as it can't evaluate properties of a property, for example if you have an Order that has an property name  Status of type
OrderStatus and that object has a  integer property named  orderStatusId.

E.g. (psuedo classes just for this example)

class Order
{
    private OrderStatus Status;

}

class OrderStatus
{
    private int OrderStatusId;

}

To retrieve a list of orders with orderstatus 's that match pending or shipping where pending=1 and shipped = 2 you may try :

IList Orders = session.CreateCriteria(typeof(Order)).Add(Expression.In("Status.OrderStatusId", states )).List();

where the 'states' argument is an ArrayList of order states, e.g. pending=1, shipping=2,.

This doesn't work as the Expression.In can't evaluate a property of a property, namely the Status.OrderStatusId from the Order object. 

You could rewrite this using a bunch of "OR keywords" in the query but doing a bunch of string manipulation however  a concatenated query string can lead to SQL injection vulnerabilities and you have to figure out how many "OR statements" there are and if there is only one state to check for there is no OR at all, the code ends up being more complicated and verbose than desired. 

This query is being driven by four check boxes on a form to determine which order statuses the user wishes to see so one could be checked (no OR keywords would be created)

e.g.

from totowebapp.DomainModel.Order as o where o.Status.OrderStatusId =1

Or could include all four checkboxes

from totowebapp.DomainModel.Order as o where o.Status.OrderStatusId = 1 OR o.Status.OrderStatusId=2 OR o.Status.OrderStatusId = 3 o.Status.OrderStatusId =4

So an easier way is using the Query object and the "in" keyword

for example :  we create an ArrayList named states with the values 1, 2, 4  (assuming pending, shipped and canceled checboxes where clicked)

    IQuery query = session.CreateQuery("from totowebapp.DomainModel.Order as o where o.Status.OrderStatusId in (:states)");
     query.SetParameterList("states",states);
      orders = query.List();   

this would result in the query

from totowebapp.DomainModel.Order as o where o.Status.OrderStatusId IN (1,2,4)

This does allow us to query against properties of a property and now we can dynamically build the set that must be matched by populating the states ArrayList.  This reduces the amount of code required to match a set of values.

Comments

[Jason]

Thanks so much for posting this! It really helped me out.

[grant]

Thank Jason, glad it helped you, it was driving me nuts at the time :)