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.

Fried Hoeben · Accepted Answer · 2010-01-12 16:28:56Z

1

Something like?

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

answered Jan 12, 2010 at 16:28

Fried Hoeben

3,27018 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joseph Daigle Over a year ago

Essentially yes. But how can this be constructed using the Criteria API?

Fried Hoeben Over a year ago

Can't help you there, I've always stuck the HQL format because I found it more powerful and easier to predict what SQL would end up getting to the database.

Joseph Daigle · Accepted Answer · 2010-01-12 20:20:02Z

0

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))

answered Jan 12, 2010 at 20:20

Joseph Daigle

48.6k11 gold badges51 silver badges74 bronze badges

Collectives™ on Stack Overflow

Creating an efficient (but complex) NHibernate query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related