title: nHibernate queryover subquery on many-to-many
body: <p>I have three tables in my database </p>
  
  <ol>
  <li>Appointment (Id, BunchOfFields)</li>
  <li>AppointmentAttendee (AppointmentId,ContactId)</li>
  <li>Contact (Id, ContactFields)</li>
  </ol>
  
  <p>An appointment can have 1 or more Attendee which is just a contact. I have these mapped: </p>
  
  <pre><code>&lt;class name="Appointment"&gt;
      &lt;set name ="Attendees" table="AppointmentAttendee"&gt;
        &lt;key column="AppointmentId"&gt;&lt;/key&gt;
        &lt;many-to-many class="Cutter.Domain.Contact" column="ContactId"/&gt;
  
      &lt;/set&gt;
  &lt;/class&gt;
  </code></pre>
  
  <p>I need to get all the appointments (with all attendees), which have a specific contact, and which starts inside a specific timeframe. So far I have:</p>
  
  <pre><code>        CurrentSession.QueryOver&lt;Appointment&gt;()
              .Where(a=&gt;a.StartDate&gt;=start &amp;&amp; a.StartDate&lt;=end)
  </code></pre>
  
  <p>What I need is basically this SQL Query</p>
  
  <pre><code>SELECT * 
  FROM Appointment a
  LEFT JOIN OtherTables....
  WHERE EXISTS (SELECT * FROM AppointmentAttendee att WHERE a.Id=att.AppointmentId and att.ContactId=?)
  </code></pre>
  
  <p><strong>EDIT</strong><br>
  So far I have come up with this:             </p>
  
  <pre><code>   var list=CurrentSession.QueryOver&lt;Appointment&gt;(() =&gt; appt)
                      .JoinAlias(()=&gt;appt.Work, ()=&gt;work)
                      .Where(a =&gt; (a.StartDate &gt;= start &amp;&amp; a.StartDate &lt;= end) 
                       &amp;&amp;   work.Status==WorkStatus.Active )
                                             .JoinQueryOver&lt;Contact&gt;(a =&gt; a.Attendees)
                      .Where(u =&gt; u.Id == assignedTo)
  
                      .List&lt;Appointment&gt;(); 
  </code></pre>
  
  <p>But I believe this is joining the appointments with the attendees, and will limit the attendees that I get back.</p>
  
  <p><strong>EDIT</strong><br>
  Some more experimenting lead me to this. (Note some of the objects changed from Contact to User but still same problem)</p>
  
  <pre><code>        Appointment appt=null;
          WorkBase work=null;
  
          var subQuery = QueryOver.Of&lt;Appointment&gt;()
              .JoinQueryOver&lt;User&gt;(a =&gt; a.InternalAttendees)             
              .Where(u =&gt; u.Id == assignedTo)
              .SelectList(a =&gt; a.Select(c=&gt;c.Id));
  
          var list=CurrentSession.QueryOver&lt;Appointment&gt;(() =&gt; appt)
              .JoinAlias(()=&gt;appt.Work, ()=&gt;work)
              .Where(a =&gt; (a.StartDate &gt;= start &amp;&amp; a.StartDate &lt;= end)
                  &amp;&amp; work.Status==WorkStatus.Active )            
              .WithSubquery.WhereExists(subQuery)              
              .List&lt;Appointment&gt;();
  </code></pre>
  
  <p>Now I am getting the subquery, but how do I connect the subquery to the parentquery. (Need subquery to reference the outer appointment id)</p>
  
  <p>Also it would be nice if I could do this without having to join the entity tables in the subquery seems wrong to have to join three tables when all the data I need is in the association table.</p>