title: Select latest group by in nhibernate
body: <p>I have Canine and CanineHandler objects in my application.  The CanineHandler object has a PersonID (which references a completely different database), an EffectiveDate (which specifies when a handler started with the canine), and a FK reference to the Canine (CanineID).</p>
  
  <p>Given a specific PersonID, I want to find all canines they're currently responsible for.  The (simplified) query I'd use in SQL would be:</p>
  
  <pre><code>Select Canine.*
      from Canine
          inner join CanineHandler on(CanineHandler.CanineID=Canine.CanineID)
          inner join 
              (select CanineID,Max(EffectiveDate) MaxEffectiveDate
                  from caninehandler
                  group by CanineID) as CurrentHandler
              on(CurrentHandler.CanineID=CanineHandler.CanineID
                  and CurrentHandler.MaxEffectiveDate=CanineHandler.EffectiveDate)
      where CanineHandler.HandlerPersonID=@PersonID
  </code></pre>
  
  <p>Edit: Added mapping files below:</p>
  
  <pre><code>&lt;class name="CanineHandler" table="CanineHandler" schema="dbo"&gt;
      &lt;id name="CanineHandlerID" type="Int32"&gt;
          &lt;generator class="identity" /&gt;
      &lt;/id&gt;
      &lt;property name="EffectiveDate" type="DateTime" precision="16" not-null="true" /&gt;
      &lt;property name="HandlerPersonID" type="Int64" precision="19" not-null="true" /&gt;
      &lt;many-to-one name="Canine" class="Canine" column="CanineID" not-null="true" access="field.camelcase-underscore" /&gt;
  &lt;/class&gt;
  
  &lt;class name="Canine" table="Canine"&gt;
      &lt;id name="CanineID" type="Int32"&gt;
          &lt;generator class="identity" /&gt;
      &lt;/id&gt;
      &lt;property name="Name" type="String" length="64" not-null="true" /&gt;
      ...
      &lt;set name="CanineHandlers" table="CanineHandler" inverse="true" order-by="EffectiveDate desc" cascade="save-update" access="field.camelcase-underscore"&gt;
          &lt;key column="CanineID" /&gt;
          &lt;one-to-many class="CanineHandler" /&gt;
      &lt;/set&gt;
      &lt;property name="IsDeleted" type="Boolean" not-null="true" /&gt;
  &lt;/class&gt;
  </code></pre>
  
  <p>I haven't tried yet, but I'm guessing I could do this in HQL.  I haven't had to write anything in HQL yet, so I'll have to tackle that eventually anyway, but my question is whether/how I can do this sub-query with the criterion/subqueries objects.</p>
  
  <p>I got as far as creating the following detached criteria:</p>
  
  <pre><code>DetachedCriteria effectiveHandlers = DetachedCriteria.For&lt;Canine&gt;()
                  .SetProjection(Projections.ProjectionList()
                      .Add(Projections.Max("EffectiveDate"),"MaxEffectiveDate")
                      .Add(Projections.GroupProperty("CanineID"),"handledCanineID")
                  );
  </code></pre>
  
  <p>but I can't figure out how to do the inner join.  If I do this:</p>
  
  <pre><code>Session.CreateCriteria&lt;Canine&gt;()
      .CreateCriteria("CanineHandler", "handler", NHibernate.SqlCommand.JoinType.InnerJoin)
      .List&lt;Canine&gt;();
  </code></pre>
  
  <p>I get an error "could not resolve property: CanineHandler of: OPS.CanineApp.Model.Canine".  Obviously I'm missing something(s) but from the documentation I got the impression that should return a list of Canines that have handlers (possibly with duplicates).  Until I can make this work, adding the subquery isn't going to work...</p>
  
  <p>I've found similar questions, such as <a href="https://stackoverflow.com/questions/747382/only-get-latest-results-using-nhibernate">Only get latest results using nHibernate</a> but none of the answers really seem to apply with the kind of direct result I'm looking for.</p>
  
  <p>Any help or suggestion is greatly appreciated.</p>