Geek Answers Handbook

Select the last group in nhibernate

I have Canine and CanineHandler objects in my application. The CanineHandler object has a PersonID (which refers to a completely different database), EffectiveDate (which indicates when the handler started with the dog), and an FK link to Canine (CanineID).

Given a specific PersonID, I want to find all the fangs for which they are currently responsible. The (simplified) query that I would 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 it yet, but I guess I can do it in HQL. I haven’t had to write anything in HQL yet, so I still have to solve it, but my question is whether I can make this subquery with criteria / subquery objects.

I went on to create the following discarded 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 make an inner join. If I do this:

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

I get the error "could not resolve property: CanineHandler from: OPS.CanineApp.Model.Canine". Obviously, I missed something, but from the documentation I got the impression that I should return a list of Canines that has handlers (possibly with duplicates). Until I can do this work, adding a subquery will not work ...

I found similar questions, such as Get only the latest results using nHibernate , but none of the answers really apply to the direct result view. looking for.

Any help or suggestion is appreciated.

0
source share
1 answer

Joining the CurrentHandler view in your example will not work in HQL the last time I checked. Try mapping a stored procedure that allows you to write whatever SQL you like. Here's what the displayed stored procedure looks like:

 <?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="S2.BP.NHSupport" namespace="S2.BP.Model"> <sql-query name="spGoGetMyDogs" callable="true"> <return-scalar column="PersonID" type="int" /> exec spGoGetMyDogs @PersonID=:personID </sql-query> </hibernate-mapping>

Then you can pass your PersonID parameter and map the NH results to your objects using such a transformer:

 public IEnumerable<Canine> LetTheDogsOut(int personID) { return nhSession.GetNamedQuery("spGoGetMyDogs") .SetInt32("personID", personID) .SetResultTransformer(Transformers.AliasToBean(typeof(Canine))) .List<Canine>(); }
+1
source

More articles:


All Articles