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 wh开发者_运维知识库ich 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 ?

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'

A better way would be relating the customer and the order in the mapping.

1. The customer class has a collection of orders as property.
2. The order class has a customer as property.
3. Both

This is a little bit more work in the mapping, but it will be less work to write the queries and the code using the customer and order objects.

if your customer have orders collection then you could use this:

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