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).

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:

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

Edit: Added mapping files below:

<class name="CanineHandler" table="CanineHandler" schema="dbo">
    <id name="CanineHandlerID" type="Int32">
        <generator class="identity" />
    </id>
    <property name="EffectiveDate" type="DateTime" precision="16" not-null="true" />
    <property name="HandlerPersonID" type="Int64" precision="19" not-null="true" />
    <many-to-one name="Canine" class="Canine" column="CanineID" not-null="true" access="field.camelcase-underscore" />
</class>

<class name="Canine" table="Canine">
    <id name="CanineID" type="Int32">
        <generator class="identity" />
    </id>
    <property name="Name" type="String" length="64" not-null="true" />
    ...
    <set name="CanineHandlers" table="CanineHandler" inverse="true" order-by="EffectiveDate desc" cascade="save-update" access="field.camelcase-underscore">
        <key column="CanineID" />
        <one-to-many class="CanineHandler" />
    </set>
    <property name="IsDeleted" type="Boolean" not-null="true" />
</class>

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.

I got as far as creating the following detached criteria:

DetachedCriteria effectiveHandlers = DetachedCriteria.For<Canine>()
                .SetProjection(Projections.ProjectionList()
                    .Add(Projections.Max("EffectiveDate"),"MaxEffectiveDate")
                    .Add(Projections.GroupProperty("CanineID"),"handledCanineID")
                );

but I can't figure out how to do the inner join. If I do this:

Session.CreateCriteria<Canine>()
    .CreateCriteria("CanineHandler", "handler", NHibernate.SqlCommand.JoinType.InnerJoin)
    .List<Canine>();

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...

I've found similar questions, such as http://stackoverflow.com/questions/747382/only-get-latest-results-using-nhibernate but none of the answers really seem to apply with the kind of direct result I'm looking for.

Any help or suggestion is greatly appreciated.