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