NHibernate QueryOver with sub-query and alias

Question

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

dotjoe · Accepted Answer · 2011-10-12 15:27:18Z

4

try

.Where(shipment => shipment.Subscription.Id == subscription.Id)

answered Oct 12, 2011 at 15:27

dotjoe

27.1k5 gold badges67 silver badges78 bronze badges

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

6 Comments

dotjoe Over a year ago

I'd guess you can't use the alias variable for implicit Id comparison. I think you could only do it that way if you were using an actual (loaded) Subscription object.

Asbjørn Ulsberg Over a year ago

I see. A bit un-intuitive given how ID comparison is usually done in NHibernate, but as long as it works, I'm happy. Thanks! :) I've updated my question regarding the missing distinct keyword in the resulting SQL, do you have any idea why it is missing?

dotjoe Over a year ago

The distinctrootentitytransformer is not the same as the SQL select distinct . That transformer is there for when you join a collection into the query and want to make sure you don't get duplicate root entities back. It does all the work on the client side.

Asbjørn Ulsberg Over a year ago

So if I want a distinct in the resulting SQL, what should I do?

dotjoe Over a year ago

I'm not sure if you can do a select distinct in NH. Since you'd have a primary key in the Subscription table, that would make distinct pointless. You can get the equivalent of distinct by doing a projection with group by...SelectList + SelectGroup

|

Collectives™ on Stack Overflow

NHibernate QueryOver with sub-query and alias

Update

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Update

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related