Hi everybody,
I'm getting crazy trying to use NHibernate with a legacy database model.
The model is a very complex one, with multiple inheritances and references, and I'm mapping the database using the table-per-class strategy.
I have a main group of classes, which inheritance is defined in this tree:
Code:
OBJECT-ITEM
|
+---FACILITY (is a OBJECT-ITEM)
| |
| +--- ... some sub-facilities
|
+---... some other sub-object-items
|
+---ORGANISATION (is a OBJECT-ITEM)
|
+---UNIT (is a ORGANISATION)
|
+---CONVOY (is a ORGANISATION)
The C# implementation of the main object tree is:
Code:
public class ObjectItem: DaoBasic {
private System.Double id= 0;
virtual public System.Double Id {
get { return id; }
}
private string objectItemCategoryCode= null;
virtual public string ObjectItemCategoryCode {
get { return objectItemCategoryCode; }
set { objectItemCategoryCode = value; }
}
private System.String objectItemName= null;
virtual public System.String ObjectItemName {
get { return objectItemName; }
set { objectItemName = value; }
}
private System.String objectItemAlternateIdentificationText= null;
virtual public System.String ObjectItemAlternateIdentificationText {
get { return objectItemAlternateIdentificationText; }
set { objectItemAlternateIdentificationText = value; }
}
private System.Double ownerId= 0;
virtual public System.Double OwnerId {
get { return ownerId; }
set { ownerId = value; }
}
private string updateSeqnr= null;
virtual public string UpdateSeqnr {
get { return updateSeqnr; }
set { updateSeqnr = value; }
}
}
public class Facility: ObjectItem {
// Implementation of facility
}
// Many other classes definition inheriting from ObjectItem
public class Organisation: ObjectItem {
private string organisationCategoryCode= null;
virtual public string OrganisationCategoryCode {
get { return organisationCategoryCode; }
set { organisationCategoryCode = value; }
}
private System.String organisationNicknameName= null;
virtual public System.String OrganisationNicknameName {
get { return organisationNicknameName; }
set { organisationNicknameName = value; }
}
private System.Double ownerId= 0;
virtual public new System.Double OwnerId {
get { return ownerId; }
set { ownerId = value; }
}
private string updateSeqnr= null;
virtual public new string UpdateSeqnr {
get { return updateSeqnr; }
set { updateSeqnr = value; }
}
}
public class Unit: Organisation {
private System.String unitFormalAbbreviatedName= null;
[DatamodelMap("unit-formal-abbreviated-name")]
virtual public System.String UnitFormalAbbreviatedName {
get { return unitFormalAbbreviatedName; }
set { unitFormalAbbreviatedName = value; }
}
private System.Double ownerId= 0;
virtual public new System.Double OwnerId {
get { return ownerId; }
set { ownerId = value; }
}
private string updateSeqnr= null;
virtual public new string UpdateSeqnr {
get { return updateSeqnr; }
set { updateSeqnr = value; }
}
}
public class Convoy: Organisation {
// And so on...
}
Mapping of the main object tree:
Code:
<class name="ObjectItem" table="OBJ_ITEM">
<id name="id" column="obj_item_id" access="field">
<generator class="increment" />
</id>
<property name="objectItemCategoryCode" column="cat_code" access="field" />
<property name="objectItemName" column="name" access="field" />
<property name="objectItemAlternateIdentificationText" column="altn_identific_txt" access="field" />
<property name="ownerId" column="owner_id" access="field" />
<property name="updateSeqnr" column="update_seqnr" access="field" />
<joined-subclass name="Facility" table="FAC">
<key column="fac_id" />
<!-- mapping of FACILITY -->
</joined-subclass>
<!-- mapping of other sub-classes -->
<joined-subclass name="Organisation" table="ORG">
<key column="org_id" />
<property name="organisationCategoryCode" column="cat_code" access="field" />
<property name="organisationNicknameName" column="nickname_name" access="field" />
<property name="ownerId" column="owner_id" access="field" />
<property name="updateSeqnr" column="update_seqnr" access="field" />
<joined-subclass name="Unit" table="UNIT">
<key column="unit_id" />
<property name="unitFormalAbbreviatedName" column="formal_abbrd_name" access="field" />
<property name="ownerId" column="owner_id" access="field" />
<property name="updateSeqnr" column="update_seqnr" access="field" />
</joined-subclass>
<joined-subclass name="Convoy" table="CONVOY">
<key column="convoy_id" />
<property name="convoyDaySpeedRate" column="day_speed_rate" access="field" />
<property name="convoyDayVehicleGapDimension" column="day_vehicle_gap_dim" access="field" />
<property name="convoyHaltDuration" column="halt_dur" access="field" />
<property name="convoyNightSpeedRate" column="night_speed_rate" access="field" />
<property name="convoyNightVehicleGapDimension" column="night_vehicle_gap_dim" access="field" />
<property name="convoyPacketGapDimension" column="packet_gap_dim" access="field" />
<property name="convoyPacketSizeQuantity" column="packet_size_qty" access="field" />
<property name="ownerId" column="owner_id" access="field" />
<property name="updateSeqnr" column="update_seqnr" access="field" />
</joined-subclass>
</joined-subclass>
</class>
It is possible to do
Code:
System.Collections.IList list = session.CreateCriteria().List(typeof(ObjectItem));
This is possible too
Code:
System.Collections.IList list = session.CreateCriteria().List(typeof(Unit)); // Subclass of Organisation
But there is an exception here!
Code:
System.Collections.IList list = session.CreateCriteria().List(typeof(Organisation)); // Subclass of ObjectItem
Second Problem:So far it would't be a big issue because it could be possible to query using the root class, but unfortunately there are some
other classes which have a foreign-key to ORGANISATION objects.
For example, the class definition is
Code:
public class ReportingData: DaoBasic {
private System.Double id= 0;
virtual public System.Double Id {
get { return id; }
}
private System.String reportingDataReportingDate= null;
virtual public System.String ReportingDataReportingDate {
get { return reportingDataReportingDate; }
set { reportingDataReportingDate = value; }
}
private System.String reportingDataReportingTime= null;
virtual public System.String ReportingDataReportingTime {
get { return reportingDataReportingTime; }
set { reportingDataReportingTime = value; }
}
private string reportingDataSourceTypeCode= null;
virtual public string ReportingDataSourceTypeCode {
get { return reportingDataSourceTypeCode; }
set { reportingDataSourceTypeCode = value; }
}
private System.Double ownerId= 0;
virtual public System.Double OwnerId {
get { return ownerId; }
set { ownerId = value; }
}
private string updateSeqnr= null;
virtual public string UpdateSeqnr {
get { return updateSeqnr; }
set { updateSeqnr = value; }
}
// ... Some other members ...
private Organisation reportingDataReportingOrganisationId= null;
virtual public Organisation ReportingDataReportingOrganisationId {
get { return reportingDataReportingOrganisationId; }
set { reportingDataReportingOrganisationId = value; }
}
}
And the map file
Code:
<class name="ReportingData" table="RPTD">
<id name="id" column="rptd_id" access="field">
<generator class="increment" />
</id>
<property name="reportingDataReportingDate" column="rep_date" access="field" />
<property name="reportingDataReportingTime" column="rep_time" access="field" />
<property name="ownerId" column="owner_id" access="field" />
<property name="updateSeqnr" column="update_seqnr" access="field" />
<!-- some other properties -->
<many-to-one name="reportingDataReportingOrganisationId" class="Organisation" column="rep_org_id" access="field" />
</class>
In this case, when accessing the Organisation field inside a ReportingData object, the same (or very similar) exception is thrown.
The text description of the exception is:
could not load an entity: [CCIS.Database.Dao.C2iedm.Organisation#270010000000210][SQL: SELECT organisati0_.org_id as obj1_21_0_, organisati0_.cat_code as cat2_44_0_, organisati0_.nickname_name as nickname3_44_0_, organisati0_.owner_id as owner4_44_0_, organisati0_.update_seqnr as update5_44_0_, organisati0_1_.cat_code as cat2_21_0_, organisati0_1_.name as name21_0_, organisati0_1_.altn_identific_txt as altn4_21_0_, organisati0_1_.owner_id as owner5_21_0_, organisati0_1_.update_seqnr as update6_21_0_, organisati0_2_.formal_abbrd_name as formal2_45_0_, organisati0_2_.owner_id as owner3_45_0_, organisati0_2_.update_seqnr as update4_45_0_, organisati0_3_.day_speed_rate as day2_46_0_, organisati0_3_.day_vehicle_gap_dim as day3_46_0_, organisati0_3_.halt_dur as halt4_46_0_, organisati0_3_.night_speed_rate as night5_46_0_, organisati0_3_.night_vehicle_gap_dim as night6_46_0_, organisati0_3_.packet_gap_dim as packet7_46_0_, organisati0_3_.packet_size_qty as packet8_46_0_, organisati0_3_.owner_id as owner9_46_0_, organisati0_3_.update_seqnr as update10_46_0_, decode (, organisati0_2_.unit_id, 24, organisati0_3_.convoy_id, 25, organisati0_.org_id, 23,0 ) as clazz_0_ FROM ORG organisati0_, OBJ_ITEM organisati0_1_, UNIT organisati0_2_, CONVOY organisati0_3_ WHERE organisati0_.org_id=organisati0_1_.obj_item_id and organisati0_.org_id=organisati0_2_.unit_id(+) and organisati0_.org_id=organisati0_3_.convoy_id(+) AND organisati0_.org_id=?]
Obviously the SQL code generated has an error at
decode (,
Hibernate version:
NHibernate 1.2.0.GA
Code between sessionFactory.openSession() and session.close():
if (sessionFactory != null) {
ISession session = sessionFactory.OpenSession();
System.Collections.IList list = session.CreateCriteria(typeof(ReportingData)).List();
foreach (object o in list) {
Inform("Object: {0}\r\n", o.ToString());
}
session.Close();
session.Dispose();
}
Name and version of the database you are using:
Oracle 10
The generated SQL (show_sql=true):
See Exception description
Does anybody know a solution to this problem?
Thank you in advance!
Login
FAQ
Search