Thanks for the tip – that’s what I did. I may look into it further if/when I have time.
Regards, Remi. From: [email protected] [mailto:[email protected]] On Behalf Of Diego Mijelshon Sent: March 16, 2010 4:12 PM To: nhusers Subject: Re: [nhusers] Improper loading of joined instance in ISqlQuery HQL _DOES_ support the having clause... so you might want to remove the SQL query completely. Diego 2010/3/16 Rémi Després-Smyth <[email protected]> I've got a problem using an ISQLQuery with an AddJoin. The entity I'm trying to return is RegionalFees, which has a composite-id which includes a Province instance. (This is the instance being improperly loaded.) Here's the mapping: <class name="Project.RegionalFees, Project" table="tblRegionalFees"> <composite-id name="Id" class="Project.RegionalFeesId, project" unsaved-value="any" access="property"> <key-many-to-one class="Project.Province, Project" name="Region" access="property" column="provinceId" not-found="exception" /> <key-property name="StartDate" access="property" column="startDate" type="DateTime" /> </composite-id> <property name="SomeFee" column="someFee" type="Decimal" /> <property name="SomeOtherFee" column="someOtherFee" type="Decimal" /> <!-- Other unrelated stuff --> </class> <class name="Project.Province, Project" table="trefProvince" mutable="false"> <id name="Id" column="provinceId" type="Int64" unsaved-value="0"> <generator class="identity" /> </id> <property name="Code" column="code" access="nosetter.pascalcase-m-underscore" /> <property name="Label" column="label" access="nosetter.pascalcase-m-underscore" /> </class> Here's my query method: public IEnumerable<RegionalFees> GetRegionalFees() { // Using an ISQLQuery cause there doesn't appear to be an equivalent of // the SQL HAVING clause, which would be optimal for loading this set const String qryStr = "SELECT * " + "FROM tblRegionalFees INNER JOIN trefProvince " + "ON tblRegionalFees.provinceId=trefProvince.provinceId " + "WHERE EXISTS ( " + "SELECT provinceId, MAX(startDate) AS MostRecentFeesDate " + "FROM tblRegionalFees InnerRF " + "WHERE tblRegionalFees.provinceId=InnerRF.provinceId " + "AND startDate <= ? " + "GROUP BY provinceId " + "HAVING tblRegionalFees.startDate=MAX(startDate))"; var qry = NHibernateSessionManager.Instance.GetSession().CreateSQLQuery(qryStr); qry.SetDateTime(0, DateTime.Now); qry.AddEntity("RegFees", typeof(RegionalFees)); qry.AddJoin("Region", "RegFees.Id.Region"); return qry.List<RegionalFees>(); } The odd behavior I’m seeing is that when I call GetRegionalFees (whose goal is to load just the most recent fee instances per region), it all loads fine if the Province instance is a proxy. If, however, Province is not loaded as a proxy, the Province instance which is part of RegionalFees' RegionalFeesId property has the correct Id, but null Code and Region values, which is incorrect. (These do have values in the database.) It looks to me like I have a problem in how I'm joining the Province class - since if it's lazy loaded the id is set from tblRegionalFees, and it gets loaded independently afterwards - but I haven't been able to figure out the solution. I expect I’m doing something wrong here. Can anyone offer a solution? Regards, Remi. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected] <mailto:nhusers%[email protected]> . For more options, visit this group at http://groups.google.com/group/nhusers?hl=en. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
