I'm struggling to convert the following (simplified) HQL to QueryOver:
select subscription
from Subscription as subscription
where not exists (
from Shipment as shipment
where shipment.Subscription = subscription
and (shipment.DeliveryDate = :deliveryDate)
)
I've come this far:
Subscription subscription = null;
Session.QueryOver(() => subscription)
.Where(Subqueries.NotExists(QueryOver.Of<Shipment>()
.Where(shipment => shipment.Subscription == subscription)
.And(shipment=> shipment.DeliveryDate == deliveryDate)
.Select(shipment => shipment.Id).DetachedCriteria));
.TransformUsing(new DistinctRootEntityResultTransformer());
The problem is that the above Subqueries
and Where
statement gives me the following (invalid) where clause:
where shipment.SubscriptionId is null
when what I want is:
where shipment.SubscriptionId = subscription.Id
So the alias and its row-level value isn't taken into account when constructing the SQL, instead its initial value null
is used to compare to the Shipment
's SubscriptionId
.
Update
With dotjoe's provided solution, I was able to write the QueryOver
statement as follows:
Subscription subscription = null;
Session.QueryOver(() => subscription)
.WithSubquery.WhereNotExists(QueryOver.Of<Shipment>()
.Where(shipment => shipment.Subscription.Id == subscription.Id)
.And(shipment => shipment.DeliveryDate == deliveryDate)
.Select(shipment => shipment.Id));
try