CODEWITHSUNDEEP
c#nhibernatequeryover
dzookatz
dzookatz

Reputation: 223

NHibernate multiple subqueries with in clause

I have a following working SQL query:

SELECT * FROM truck t
WHERE t.currentlocationdbid IN (SELECT dbid FROM location WHERE name = 'Los Angeles')
OR t.nextdestinationdbid IN (SELECT dbid FROM location WHERE name = 'Chicago' OR name = 'New York');

I'd like to write this in NHibernate. With multiple trips to DB for each entity it works, of course, but I'd like to make it a one trip. Looked into examples with detached queries like this, this or this but none worked for me. Tried to do it also with aliases and criterias.

One of dozens of attempts:

var subQuery1 = QueryOver.Of<LocationEntity>().Where(l => l.Name == LocationNameEnum.LA);
var subQuery2 = QueryOver.Of<LocationEntity>().Where(l => l.Name == LocationNameEnum.CHG || l.Name == LocationNameEnum.NY);

var poc = session.QueryOver<TruckEntity>()
                 .WithSubquery.WhereProperty(t => t.CurrentLocation).In(subQuery1)
                 .WithSubquery.WhereProperty(t => t.NextDestination).In(subQuery2)
                 .List<TruckEntity>();

Thanks in advance for any suggestion.

Upvotes: 3

Views: 1272

Answers (1)

Florian Lim
Florian Lim

Reputation: 5362

You got it almost right, you are only missing the .Where(Restrictions.Disjunction()...) for the or in SQL.

Based on your code (assuming that you have a property Id in LocationEntity):

// get IDs to look for in CurrentLocation
var subQuery1 = QueryOver.Of<LocationEntity>()
    .Where(l => l.Name == LocationNameEnum.LA)
    .Select(x => x.Id);

// get IDs to look for in NextDestination
var subQuery2 = QueryOver.Of<LocationEntity>()
    .Where(l => l.Name == LocationNameEnum.CHG || l.Name == LocationNameEnum.NY)
    .Select(x => x.Id);

var poc = session.QueryOver<TruckEntity>()
    .Where(Restrictions.Disjunction() // this takes care of the OR
        .Add(Subqueries.WhereProperty<TruckEntity>(x => x.CurrentLocation.Id).In(subQuery1))
        .Add(Subqueries.WhereProperty<TruckEntity>(x => x.NextDestination.Id).In(subQuery2))
    )
    .List<TruckEntity>();

Upvotes: 3

Related Questions