How do I express a query containing a WHERE..IN subquery using NHibernate's QueryOver API?

Question

I have an object model where an Order contains many LineItems, and each LineItem has an associated Product. In the object model, these are one-way associations -- a LineItem does not know anything about its Order.

Class diagram

I want to query for orders that contain a line item with a product name matching a string, returning one row for each order (so that paging can be performed).

SELECT * FROM Orders 
WHERE OrderID IN (
    SELECT DISTINCT OrderID 
    FROM LineItems 
    INNER JOIN Products on LineItems.ProductID = Products.ProductID
    WHERE Products.Name = 'foo'
)

Given that I have an ICriteria or an IQueryOver representing the subquery, how do I actually apply it to my root Order query?

var subquery = QueryOver.Of<LineItem>
                        .Where(l => l.Product.Name == "foo")
                        .TransformUsing(Transformers.DistinctRootEntity);

I've found plenty of examples that assume the root object in the query is on the "many" side of a one-to-many relationship, but I can't figure out how to add a restriction on something that the root object has many of.

If the LineItem does not have a property of Order. then how did u project out OrderId in your example SQL ? — frictionlesspulley, Dec 21 '12 at 18:38

dotjoe · Accepted Answer · 2013-01-02T22:12:18.963

I'd make it a bi-directional relationship between order and line item to allow efficient queries (reduce the number of joins required). But, if for some weird reason you can't, you'll need to start the sub-query from the Order...

LineItem lineItemAlias = null;
Product productAlias = null;

var subQuery = QueryOver.Of<Order>()
            .JoinAlias(x => x.LineItems, () => lineItemAlias)
            .JoinAlias(() => lineItemAlias.Product, () => productAlias)
            .Where(() => productAlias.Name == "foo")
            .Select(Projections.Group<Order>(x => x.Id));

var results = Session.QueryOver<Order>()
              .WithSubquery.WhereProperty(x => x.Id).In(subQuery)
              .List();

frictionlesspulley · Answer 2 · 2012-12-21T18:48:28.477

2

The direct translation of the SQL that you provided can be acheived using this

var subQuery = 
      QueryOver.Of<LineItem>(() => lineItem)
            .JoinAlias(() => lineItem.Products, () => product)
            .Where(() => product.Name == "foo")
            .Select(Projections.Distinct(
                      Projections.Property(()=> lineItem.Order.Id)));;

var theQueryYouNeed =  
               QueryOver.Of<Orders>(() => order)
              .WithSubquery.WherePropertyIn(() => order.Id).In(subQuery);

However if your LineItem entity does not have a Order Property then you cannot really use the subquery.

If you need to find

All Orders which have a LineItem where the Product Name is "foo" then

var theQueryYouNeed = 
  QueryOver.Of<Orders>(() => order)
     .JoinAlias(() => order.LineItems, () => lineItem)
     .JoinAlias(() => lineItem.Product, () => product)
     .Where(() => product.Name == "foo")
     .TransformUsing(new DistinctRootEntityResultTransformer())

edited Dec 21 '12 at 18:48

answered Dec 21 '12 at 18:29

frictionlesspulley

11,070
14
66
115

You might have to work on adding restrictions on the subquery correctly as I did not know exactly the relationship names LineItem.. you get the general Idea – frictionlesspulley Dec 21 '12 at 18:30
The problem is that my `LineItem`s don't have an `Order` property, so there's no way to express in my subquery that I want the OrderID. Can I do this by mixing QueryOver and ICriteria? – Brant Bobby Dec 21 '12 at 18:31
The QueryOver I wrote was by looking at the SQL you posted.. Where is the OrderId defined (on which Entity) in the inner SQL in your example – frictionlesspulley Dec 21 '12 at 18:33
The 'LineItems' database table has an OrderID column, but the `LineItem` class does not have an `Order` or an `OrderId` property. – Brant Bobby Dec 21 '12 at 20:48
Does the second query help? – frictionlesspulley Dec 21 '12 at 20:51
The second query does work for my example, but since the generated SQL uses inner joins instead of subqueries, it results in a cartesian product of Orders x LineItems which means I can't page over the result set. – Brant Bobby Dec 21 '12 at 21:09
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/21543/discussion-between-frictionlesspulley-and-brant-bobby) – frictionlesspulley Dec 21 '12 at 21:18

How do I express a query containing a WHERE..IN subquery using NHibernate's QueryOver API?

2 Answers2

Linked