Nhibernate QueryOver join on multiple columns

Question

I am trying to reproduce this SQL query in NH:

select vehicle.Id, price.Price
from vhc.Vehicle vehicle
    left outer join vhc.VehiclePrice price on vehicle.Id = price.VehicleId 
          and price.VehiclePriceTypeId = 1

where price.Id is null or price.VehiclePriceTypeId = 1
order by price.Price

The important part is the second join criteria. I want to see all Vehicles, regardless of whether they have a price, but if they have any prices, I want to select the price of type 1. Removing that second join criteria means that it excludes all vehicles that only have prices of types 2, 3, etc. That's not ok.

Approaches I've tried:

Adding a global filter on the VehiclePrice object to filter only on VehiclePriceType = 1 but it puts it on the Where, not on the Join, so no luck there.
Adding a SubQuery for prices with type 1, but, again, it applies it on the Where, not on the Join.
Other join types -- Full, Right... don't seem to make sense here.

It's a common problem, just haven't found the solution yet. Any thoughts?

Shagglez · Accepted Answer · 2012-05-03 19:45:17Z

2

This is now possible with QueryOver (I don't know as of when, however I tried to do this before and could not do so).

var vpAlias = null;
var prices = session.QueryOver<Vehicle>()
    .Left.JoinAlias<VehiclePrice>(x => x.VehiclePrice, () => vpAlias, x => x.VehiclePriceTypeId == 1)
    .Where(() => vpAlias.Id == null || vpAlias.VehiclePriceTypeId == 1)
    .Select(x => x.Id, () => vpAlias.Price)
    .ToList();

You will need to create an alias for the entity on which you would like to join on multiple fields, however it seems you can do so with either JoinAlias or JoinQueryOver, they just return different entities.

answered May 3, 2012 at 19:45

Shagglez

1,5324 gold badges21 silver badges38 bronze badges

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

1 Comment

awright Over a year ago

Are you running the new 3.3 version? I haven't tried multiple joins on that version yet.

Firo · Accepted Answer · 2011-09-07 16:14:48Z

1

just an idea: use 2 Queries in one roundtrip

var vehiclesWithPrice = session.QueryOver<Vehicle>()
    .JoinQueryOver<VehiclePrice>(vehicle => vehicle.Prices, () => vpricealias)
    .Where(price => price.VehiclePriceTypeId == 1)
    .OrderBy(() => vpricealias.Price).Asc
    .Select(vehicle => vehicle.Id, vehicle => vpricealias.Price)
    .Future();

var vehiclesWithoutPrice = session.QueryOver(() => vehiclealias)
    .WithSubquery.WhereNotExists(QueryOver.Of<VehiclePrice>()
        .Where(price => price.Vehicle.Id == vehiclealias.Id)
        .Where(price => price.VehiclePriceTypeId == 1)
    )
    .Select(vehicle => vehicle.Id, vehicle => 0)
    .Future();

var vehiclesprices = vehiclesWithoutPrice.Concat(vehiclesWithPrice);

answered Sep 7, 2011 at 16:14

Firo

30.8k4 gold badges57 silver badges97 bronze badges

1 Comment

awright Over a year ago

Thanks. Will keep this on my radar. HQL provided for this using a WITH clause. Is there an equivalent in the QueryOver API? nhforge.org/blogs/nhibernate/archive/2009/05/17/…

Collectives™ on Stack Overflow

Nhibernate QueryOver join on multiple columns

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related