NHibernate Criteria Where any element of list property is true

Question

I have an already created NHibernate Criteria query that I need to modified so I can add a new condition.

The query is based on the Order object, which has a list of OrderItems and then, every OrderItem has a bool property named FinalDeliveryIndicator.

In my Criteria query I need to add a condition in which I want all orders that at least one of its OrderItems has the FinalDeliveryIndicator bool set to true.

The query, at the moment, is:

var search = NHibernateSession.CreateCriteria(typeof(Order))
            .CreateAlias("Contract", "C", JoinType.InnerJoin)
            .CreateAlias("C.Supplier", "S", JoinType.InnerJoin)
            .Add(Restrictions.Eq("Buyer.Id", companyId))
            .Add(Restrictions.Eq("IsDeleted", false))
            .Add(Restrictions.Eq("IsActiveVersion", true))
            .SetFirstResult(paging.PageIndexSQL)
            .SetMaxResults(paging.PageSize)
            .AddOrder(SortOrder.Desc("Id"));

Now I need to add that condition I told you about. This query is already in use at many places on this application and so I cannot switch to QueryOver or some other type of query, due to the risk of crashing something.

Answer 1

What we would need is Sub-SELECT. This could be achieved with subquery.

15.8. Detached queries and subqueries

We can define subquery with DetachedCriteria:

var subquery = DetachedCriteria.For<OrderItem>()
    .Add(Restrictions.Eq("FinalDeliveryIndicator", true))
    .SetProjection(Projections.Property("OrderId"));

This would later end up as this SQL snippet:

(SELECT OrderId FROM OrderItems WHERE FinalDeliveryIndicator = 1 )

And this subquery we can use as a part of WHERE in our main query

...
search.Add(Subqueries.PropertyIn("Id", subquery))
...

Which would add this restriction into WHERE clause:

SELECT ...
FROM Order this_
JOIN ...
WHERE ...
AND this_.OrderId IS IN // the above subquery
        (SELECT OrderId FROM OrderItems WHERE FinalDeliveryIndicator = 1 )