NHibernate Join and Restriction Criteria

Question

Hi,

Im trying to write an NHibernate criteria that effectively joins and restricts at the same time. My DB looks like this...

Cases ---> CustomerProducts <--- Customers Cases ---> CaseStatuses

Each case is associated with a customer product (Many cases to one product).

Each customer has a number of customer products (One customer has many customer products).

Each case additionally has a case status (one case status to many cases).

This has been mapped with XML files and the many to many resolution between cases and customers (through customer products) has been resolved with a Sets<> in the CustomerProduct mapping meaning that the CustomerProduct entity has sets:

Customers

Cases

I then create a criteria typed to "Cases". The criteria I need to apply is....

1. Statuses IN [varioud status codes]. This has been acheived with....

   criteria.Add(Restrictions.In("CaseStatus.CaseStatusId", statuses));

2. Now I need to select only cases for a specific customer Id. I have tried...

   criteria.Add("CustomerProduct.Customer.CustomerId", customerId);

This doesnt work and NHibernate tells me that it cannot resolve the mapping to CustomerProduct.Customer.CustomerId.

Case has a property of a CustomerProduct object.

CustomerProduct has a property of a Customer object.

Customer has a property of CustomerId.

Any ideas why it wont work?

Thanks.

Answer 1

I think you need to create an alias for CustomerProduct.Customer:

criteria.CreateAlias("CustomerProduct", "customerProduct");
criteria.CreateAlias("customerProduct.Customer", "customer");
criteria.Add(Restrictions.Eq("customer.CustomerId", customerId));

I'm surprised the IN restriction worked without an alias. Here's a good article about performing join queries with the Criteria API.