Nhibernate ICreteria relation between tables

Question

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 ?

Answer 1

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'

Answer 2

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.

Answer 3

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

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