Nhibernate Subquery using criteria API

If you ever worked with relational databases then higher chances are you already worked with sub queries. Some time while working with Nhibernate you get yourself into some situation where you need sub query to do some lifting for you. In NHibernate criteria API you can do the sub query using DetachedCriteria and Subqueries classes. As the name suggests DetachedCriteria helps you define your sub query criteria while Subqueries class help you create sub query criterion object using detached criteria object.

In order to demonstrate sub query implementation lets consider an example. Suppose we have three entities Order, OrderItem and Product where one order can more have more then one order items while one order item may be linked to a product. If we need to do a query where we need to list all those order items which are linked to products and have price greater then $100. Also the products must be the ones marked as special in system. The SQL for such query may be something like below

SELECT * FROM OrderItem AS OI 
WHERE OI.Price > 100 AND OI.ProductId IN ( SELECT P.ProductId FROM Product WHERE P.IsSpecial = 1 )

The same can be achieved by using NHibernate criteria API with the use of Subqueries and DetachedCriteria class. Above query can translated to follwoing NHibernate criteria query

ICriteria criteria = session.CreateCriteria<OrderItem>("OI")
                .Add(Restrictions.Gt("OI.Price", 100));

DetachedCriteria subQuery = DetachedCriteria.For<Product>("P")
                .Add(Restrictions.Eq("P.IsSpecial", true));

var items = criteria.Add(Subqueries.PropertyIn("OI.ProductId", subQuery))
                .List<OrderItem>();

That's it!