ISQLQuery using AddJoin not loading related instance
- by Remi Despres-Smyth
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.
