ISQLQuery using AddJoin not loading related instance

Question

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 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 here 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 transparent proxy, the Province instance which is part of RegionalFees' RegionalFeesId property has null Code and Region values, although the Id value is set correctly.

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.

Answer 1

well i can't see the mistake, although you are doing

qry.AddEntity("RegFees", typeof(RegionalFees));

and i don't see anything aliased as 'RegFees'.

Nevertheless is there a particular reason you are doing this with an ISQLQuery? you can do it with an HQLQuery , check chapter "13.10. The Group By Clause" in the latest nhibernate reference