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].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
