nhibernatequeryover

nHibernate QueryOver with a Left Join using *OR* and *AND* clauses


After some hours looking for the answer I failed. Maybe is too simple and no one had never talked about it, maybe I didn't used the right terms to search.

Basically I need to use OR and AND clause on a LEFT JOIN table. And I wish to do that in a QueryOver. The resulting SQL should look like this:

SELECT customer.name, order.id
  FROM customer
  LEFT JOIN order ON (order.idcustomer = customer.id)
 WHERE customer.active
   AND (order.id IS NULL or (order.date >= :p0 AND order.date <= :p1)

Which means, all active customers and their orders, BUT only those who never made an order or those who have orders between a specific period.

The most close to this I got in QueryOver was:

Order order = null;

var query = session.QueryOver<Customer>()
   .Left.JoinAlias(x => x.Orders, () => order)
   .Where(x => x.Active)
   .And(() => order == null || (order.date >= date1 && order.date <= date2))

The above QueryOver, before even is executed, gives me 'Reference not set'. I found the instructions about using 'Restrictions', but none (or at least, none clear enough to my limited mind) using OR and AND together.

I appreciate any help.


Solution

  • I think you're just missing a .Id on order in your where clause:

    Order order = null;
    
    var query = session.QueryOver<Customer>()
       .Left.JoinAlias(x => x.Orders, () => order)
       .Where(x => x.Active)
       .And(() => order.Id == null || (order.date >= date1 && order.date <= date2))
    // -----------------^
    

    This gives me the following SQL (SQL Server):

    SELECT
        this_.Name as y0_,
        order1_.Id as y1_ 
    FROM
        Customer this_ 
        LEFT OUTER JOIN [Order] order1_ ON this_.Id = order1_.CustomerId
    WHERE
        this_.Active = @p0 AND (
            order1_.Id IS NULL OR (
                order1_.Date >= @p1 AND order1_.Date <= @p2
            )
        );