NHibernate using QueryOver: WHERE and EXISTS

Question

I have a Show object with a child nav IList<TicketRequest> . Using QueryOver , I would like to select all Show objects that have the Active flag set, and also select Show , which will be broadcast in the future, and link TicketRequest objects. Working SQL query:

 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 cannot figure out how to make this work using QueryOver. I can get there halfway:

 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 creates SQL:

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

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

Answer 1

a bit detailed but it 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();