c# - Select latest group by in nhibernate -

I have a Kunien and Cannihandler objects in my application CanineHandler object is a PersonID (a completely different database reference), An EFFECTIVEDATE (which specifies when starting with a handler dog), and an FK reference for the dog (CanineID).

Looking at a specific PersonID, I want to find all the dogs they are currently responsible for (simplified) query I will use in SQL:

  sELECT * fROM dog inside on (joining CanineHandler CanineHandler.CanineID = Canine.CanineID) select the inner join (CanineID, Max. (effective date) Maksafektivdet (currently Handlr from Kaninhandlr group by CanineID. Kanenaidi = Kaininha Non- refundable. Do Kaneniid and Chaluhandlr. Maksafektivdet = Kanenhandlr. Kanhinhandlr as the effective date). Hendlrpisraidi = @ Praysid / << code>  
 Edit: mapping files added below:  
  & lt; class name = "caninehandler" table = "caninehandler" schema = "dbo" & gt; & Lt; id name = "CanineHandlerID" type = "Int32" & gt; & Lt; generator class = " Identity "/>   propertyProgram = "Handlrpisinaidi" type = "Int 64" precision = "19" Not- null = "true" / & gt; & Lt; Many-to-one names = "canteen" class = "canteen" column = "canidoid" no-null = "true" access = "field.mailme-underscore" /> & Lt; / Square & gt; & Lt; Class name = "canine" table = "canine" & gt; & Lt; Id name = "canineID" type = "int32" & gt; & Lt; Generator class = "identity" /> & Lt; / Id & gt; & Lt; Property name = "name" type = "string" length = "64" no-null = "true" /> ... and & lt; Set Name = "CanineHandlers" table = "CanineHandler 'reverse =" true "command-rate =" EFFECTIVEDATE desc "cascade =" save updated "access =" Fieldkcamelcase- underscore "& gt; & Lt; key column =" canineID "/> 
 One-to-many classes =" cannihandler "/> gt; & gt; & gt; property name =" isted "type =" boolean "no-null =" true "/>  
 
 I have not tried yet, but I think I can do this in HQL. I have not had to write anything, so ultimately i I have to deal with this, but my question is how can I do this sub-query with criterion / subcategory objects. 
 
 I got the form of the following different criteria: 
 
  DetachedCriteria effectiveHandlers = DetachedCriteria.For & LT; dog & gt; () .SetProjection (Projections.ProjectionList () .Add (Projections.Max ( "EFFECTIVEDATE"), "Maksfektivdet"). add (projection .groups ("Canyonid"), "handdain idid");  
 
 But I can not understand how to get involved internally. If I do this: 
 
  Session.CreateCriteria & LT; Dogs & gt; () .CreateCriteria ("CanineHandler", "Handler", NHibernate.SqlCommand.JoinType.InnerJoin) .List & LT; Dogs & gt; ();  
 
 I get an error "Property can not be resolved: Caninehandler of: Ops. Canon App.Model.Canin". Obviously I am missing some (s), but the documentation gives me an impression, for which the list of the keys should be returned, the handler (possibly with a duplicate). Until I can not do this work, the sub-work is not working ... 
 
 I have got such questions as if any answer does not really apply with direct result. / P> 
 
 
 Any help or suggestion is highly appreciated. 
 On joining the derived table, presently Handler in your example, last time I checked in HQL. Try mapping a stored procedure so you can write SQL as you like. Looks like a mapped archive: 
 
  & lt ;? Xml version = "1.0" encoding = "UTF-8"? & Gt; & Lt; Hibernate-Mapping XMLNS = "Color: Nurbinet-Mapping -2.2" Assembly = "S 2. BP.NHSport" Namespace = "S2BP.Model" & gt; & Lt; Sql-query name = "spGoGetMyDogs" callable = "true" & gt; & Lt; Return-scaler column = "PersonID" type = "int" /> Exec spGoGetMyDogs @ PersonID =: personID & lt; / Sql-query & gt; & Lt; / Hibernate-mapping & gt;  
 
 You can then pass your PersonalID parameter and the results on NH can map your objects back to your object with the transformer: 
 
  Public Inemranable & lt; Canine & gt; LetTheDogsOut (integer personID) {return nhSession.GetNamedQuery ( "spGoGetMyDogs") .SetInt32 ( "personID", personID) .SetResultTransformer (Transformers.AliasToBean (typeof (Canine))) .List & LT; Dogs & gt; (); }