Q:

Nhibernate ICreteria relation between tables

I have two entities say Customer and Order which exist on their own and have no relationship defined in a mapping file.
Now I want nhibernate to give me the result of the following query:
select customer.id,customer.name from customer,order
where customer.id = order.id
and order.status = "Open"

I tried to use a projection but when examining the output sql I see Nhibernate generates a subquery which is not my intention (and less performant?)
public IList<Customer> GetOpenOrders()
{
                    DetachedCriteria orders = DetachedCriteria.For<Order>("orders")  
                    .SetProjection(Projections.Property("orders.id"));

                    ICriteria cret = session.CreateCriteria(typeof(Customer)) 
                    .Add(Subqueries.PropertyIn("id", orders)) 
                    .Add(Expression.Eq("Status", "open"));

                    return cret.List<Customer>();
}

Is it possible to do this with criterias or is there a better way to accomplish this sort of queries ?

A:

I'm not sure if you can do it with the ICriteria API because ICriteria queries are created against a specific object but you should be able to do it with HQL:
select customer
from Customer customer, Orders order
where customer.id = order.id and order.status = 'Open'

