title: nhibernate and "NOT IN" in a aggregate subquery
body: <p>I've got an entity <strong>Reminder</strong> which contains a collection of <strong>ReminderSchedule</strong>.
  This is my mapping:</p>
  
  <pre><code>&lt;class xmlns="urn:nhibernate-mapping-2.2" mutable="true" name="Reminder" table="Reminders"&gt;
      &lt;id name="Code" type="System.Guid"&gt;
          &lt;column name="ReminderCode" /&gt;
          &lt;generator class="guid.comb" /&gt;
      &lt;/id&gt;
      ...
      &lt;set access="field.pascalcase-underscore" cascade="all-delete-orphan" inverse="true" lazy="false" name="Schedules" mutable="true"&gt;
        &lt;key foreign-key="FK_Schedules_Reminders"&gt;
          &lt;column name="ReminderCode" /&gt;
        &lt;/key&gt;
        &lt;one-to-many class="ReminderSchedule" /&gt;
      &lt;/set&gt;
  &lt;/class&gt;
  </code></pre>
  
  <p>This is the mapping for the entity <strong>ReminderSchedule</strong>:</p>
  
  <pre><code>  &lt;class xmlns="urn:nhibernate-mapping-2.2" mutable="true" name="ReminderSchedule" table="ReminderSchedules"&gt;
      &lt;id name="Code" type="System.Guid"&gt;
        &lt;column name="ReminderScheduleCode" /&gt;
        &lt;generator class="guid.comb" /&gt;
      &lt;/id&gt;
      &lt;property name="NextSchedule" type="System.DateTime"&gt;
        &lt;column name="NextSchedule" index="ReminderScheduleK01" not-null="true" /&gt;
      &lt;/property&gt;
      &lt;many-to-one class="Reminder" foreign-key="FK_ReminderScheduleToReminder" name="Reminder"&gt;
        &lt;column name="ReminderCode" index="ReminderScheduleK02" not-null="true" /&gt;
      &lt;/many-to-one&gt;
    &lt;/class&gt;
  </code></pre>
  
  <p>ReminderSchedules table contains a record for each new scedule associated with a reminder. 
  I can close a schedule (Closed = 1) and reschedule a new one.<br>
  In this situation I would have a new records with Closed = 0 and I the field NextSchedule would contain the date/time of the next schedule. </p>
  
  <p>I am already using <strong>CreateCriteria</strong> to filter the reminders and it works pretty very well. 
  Now I would like to fetch the reminders which do not have any ReminderSchedule open.  </p>
  
  <p>I've figured out how to do it with a query:</p>
  
  <pre><code>SELECT * FROM Reminders
  WHERE ReminderCode 
      NOT IN (
          SELECT LastReschedule.ReminderCode FROM (
          SELECT ReminderCode, MAX(NextSchedule) MaxSchedule
          FROM ReminderSchedules
          WHERE Closed = 1 
          GROUP BY ReminderCode) LastReschedule
          )
  ORDER BY Reminders.ReminderCode
  </code></pre>
  
  <p>but I don't know how to translate it in a criteria valid for nhibernate. </p>
  
  <p>Is there anybody out there who can help me? It would be really appreciated.</p>