Need to convert a subquery that selects multiple values to an nhibernate query (criteria or hql) -

i have following query need convert nhibernate:

select  o.*  orders o inner join  (         -- recent orders based on end_date (this implies same order can exist in orders table more once)         select o2.order_id, max(o2.end_date) max_end_date          orders o2         group o2.order_id ) most_recent_orders                  on o.order_id=most_recent_orders.order_id                 , o.end_date=most_recent_orders.max_end_date -- of recent orders, ones complete                 o.is_complete=1 

i know hql doesn't support joining subqueries why doesn't work. can't use "in" statement because subquery selecting 2 values. tried using suggestion hibernate documentation:

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-tuple

from cat cat not ( cat.name, cat.color ) in (     select cat.name, cat.color domesticcat cat ) 

but threw error because sql server doesn't multiple values in "in" statement.

any appreciated! i'm open solutions using criteria or hql.

this using subquery achieve same

var maxdatequery = detachedcriteria.for<order>()     .add(restrictions.propertyeq("orderid", "order.orderid"))     .setprojection(projections.property("enddate"));  var results = session.createcriteria<order>("order")     .add(subqueries.eq("enddate",maxdatequery))     .add(restrictions.eq("iscomplete", true))     .list<order>();