CODEWITHSUNDEEP
.netnhibernatequeryover
ronny97
ronny97

Reputation: 43

NHibernate using QueryOver: WHERE and EXISTS

I have a Show entity with child nav IList<TicketRequest>. Using QueryOver, I would like to select all Show entities which have their Active flag set and also select Shows which air in the future and have associated TicketRequest entities. A working SQL query is:

select s.*
from Show s
where s.Active = 1 or
    (s.ShowDate > getdate() and exists(
     select 1
     from TicketRequest tr
     where tr.Show_id = s.Id))

I can't figure out how to make this work using QueryOver. I can get there halfway with:

Show showAlias = null;

var existing = QueryOver.Of<TicketRequest>()
        .Where(r => r.Show.Id == showAlias.Id)
        .Select(r => r.Show);

var results = Session.QueryOver<Show>(() => showAlias)
        .Where(s => s.ShowDate > DateTime.Now)
        .WithSubquery.WhereExists(existing)
        .List();

which essentially produces SQL:

select s.*
from Show s
where s.ShowDate > getdate() and exists(
     select 1
     from TicketRequest tr
     where tr.Show_id = s.Id)

Can anyone figure out how to go all the way and include the additional WHERE conditions? Any help would be appreciated.

Upvotes: 4

Views: 7331

Answers (1)

Firo
Firo

Reputation: 30813

a bit verbose but should do

var results = session.QueryOver<User>(() => showAlias)
    .Where(Restrictions.Or(
        Restrictions.Where<Show>(s => s.Active),
        Restrictions.And(
            Restrictions.Where<Show>(s => s.ShowDate > DateTime.Now),
            Subqueries.WhereExists(existing))))
    .List();

Upvotes: 6

Related Questions