Creating an efficient (but complex) NHibernate query.

Question

Here is roughly our data model (the entity names are fake and only for example purposes).

Product has a many-to-many relationship with Shipper. Shipper then has a one-to-many relationship with Warehouse.

Basically: Product has many Shippers which have many Warehouses.

We have a mapping from Product to Shipper and from Warehouse to Shipper. But NOT FROM Shipper to Product or Warehouse.

I've been trying to construct a query to return (for now just the count of) all the Warehouses which are related to a particular Product.

First Attempt: Got a list of Shippers from the Product. Created a query for Warehouse where the Shipper was IN our set. This works, but it's two queries. We need it to be one query.

Answer 1

Something like?

from warehouse w 
where w.Shipper in 
     (select p.shippers from product p where p.id = 2) 

Answer 2

Turns out you do need a mapping from Shipper to Product to make this work. But that's okay, just make it a no-access "query only" property.

Then it's as simple as doing a sub-select:

var subcriteria = DetachedCriteria.For<Shipper>()
                .CreateCriteria("Products", "productsForCategory")
                .Add(Property.ForName("productsForCategory.Id").Eq(product.Id))
                .SetProjection(Projections.Id());

And then putting that IN the other query:

Session.CreateCriteria<Warehouse>()
                .Add(Subqueries.PropertyIn("Shipper", subcriteria))