NHibernate 1.2
SQL Server 2005
.Net 3.5
I'm attempting to read everything in at once. The data set is relatively small, although I'll break it up later if it becomes a bottleneck.
If I don't do any eager loading at all it pulls everything in as expected as I touch the collections. if I eager load the abridgedSteps it still works as expected, but if I try to eager load the datafields underneath the abridgedSteps, that's when it starts having problems.
or I should say that is my intent although my criteria code is probably not correct.
The SQL looks to be generating multiple columns for the same DB column. If anyone has an idea as to why this is happening I'd really appreciate it.
Note that all test data was input into the DB via these same entities, and I've manually ran the SQL against the test data and it appears to be correct (no unexpected values, et al).
Here's the ICriteria I'm creating
Code:
ICriteria criteria = session.CreateCriteria(typeof(ProcedureLayoutModel))
.Add(Expression.Eq("id", layoutID))
.SetFetchMode("abridgedSteps", FetchMode.Eager)
.SetResultTransformer(new DistinctRootEntityResultTransformer())
.CreateCriteria("abridgedSteps")
.SetFetchMode("dataFields", FetchMode.Eager)
.SetFetchMode("fullSteps", FetchMode.Eager)
.CreateCriteria("fullSteps")
.SetFetchMode("dataFields", FetchMode.Eager);
here's the SQL that NHibernate is creating
Code:
SELECT this_.id as id3_3_, this_.optimistic_lock as optimistic2_3_3_, this_.name as name3_3_,
this_.procedure_id as procedure4_3_3_, abridgedst4_.procedurelayout_id as procedur1___5_,
abridgedst1_.id as abridged2_5_, abridgedst4_.sequence as sequence__5_,
abridgedst1_.id as id0_0_, abridgedst1_.optimistic_lock as optimistic2_0_0_,
abridgedst1_.text as text0_0_, fullsteps6_.abridgedstep_id as abridged1___5_,
fullstepmo2_.id as fullstep2_5_, fullsteps6_.sequence as sequence__5_,
fullstepmo2_.id as id16_1_, fullstepmo2_.optimistic_lock as optimistic2_16_1_,
fullstepmo2_.text as text16_1_, datafields8_.abridgedstep_id as abridged1___5_,
datafieldm9_.id as datafield2_5_, datafields8_.sequence as sequence__5_,
datafieldm9_.id as id18_2_, datafieldm9_.optimistic_lock as optimistic2_18_2_,
datafieldm9_.name as name18_2_, datafieldm9_.description as descript4_18_2_,
datafieldm9_.datatype_id as datatype5_18_2_, datafieldm9_.numeric_max as numeric6_18_2_,
datafieldm9_.numeric_min as numeric7_18_2_, datafieldm9_.metric_unit_id as metric8_18_2_
FROM ProcedureLayouts this_
inner join ProcedureLayouts_AbridgedStep abridgedst4_
on this_.id=abridgedst4_.procedurelayout_id
inner join AbridgedSteps abridgedst1_
on abridgedst4_.abridgedstep_id=abridgedst1_.id
inner join AbridgedStep_FullStep fullsteps6_
on abridgedst1_.id=fullsteps6_.abridgedstep_id
inner join FullSteps fullstepmo2_
on fullsteps6_.fullstep_id=fullstepmo2_.id
left outer join AbridgedSteps_DataFields datafields8_
on abridgedst1_.id=datafields8_.abridgedstep_id
left outer join DataFields datafieldm9_
on datafields8_.datafield_id=datafieldm9_.id
WHERE this_.id = '46600976-ea50-4008-bb2d-0e3b468fb799'
Quick overview of the entity relationships (configs are below)
Code:
ProcedureLayout
AbridgedSteps // list collection
DataFields // list collection
FullSteps // list collection
DataFields // list collection
The relevant configs
ProcedureLayout
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="Noria.Services.ProcedureDS.ProcedureLayoutModel, ProcedureDS"
optimistic-lock="version"
table="ProcedureLayouts">
<id name="id" column="id" type="Guid" >
<generator class="assigned" />
</id>
<version name="optimisticLock" column="optimistic_lock" type="Int32" />
<property name="name" column="name" type="String" />
<property name="parentProcedureID" column="procedure_id" type="Guid" />
<list name="abridgedSteps" lazy="false" table="ProcedureLayouts_AbridgedStep" cascade="none">
<key column="procedurelayout_id"/>
<index column="sequence" type="Int32" />
<many-to-many column="abridgedstep_id" class="Noria.Services.ProcedureDS.AbridgedStepModel, ProcedureDS"/>
</list>
</class>
</hibernate-mapping>
AbridgedStep
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="Noria.Services.ProcedureDS.AbridgedStepHistoryModel, ProcedureDS"
table="History__AbridgedSteps">
<composite-id name="historyID" class="Noria.Services.ProcedureDS.HistoryCompositeID, ProcedureDS">
<key-property name="recordID" column="record_id" />
<key-property name="version" column="record_version" />
</composite-id>
<!-- dataFields -->
<list name="dataFields" lazy="false" table="History__AbridgedSteps_DataFields" cascade="none">
<key>
<column name="record_id" />
<column name="record_version" />
</key>
<index column="sequence" type="Int32" />
<many-to-many class="Noria.Services.ProcedureDS.DataFieldHistoryModel, ProcedureDS">
<column name="datafield_id" />
<column name="datafield_version" />
</many-to-many>
</list>
<!-- fullSteps -->
<list name="fullSteps" lazy="false" table="History__AbridgedStep_FullStep" cascade="none">
<key>
<column name="record_id" />
<column name="record_version" />
</key>
<index column="sequence" type="Int32" />
<many-to-many class="Noria.Services.ProcedureDS.FullStepHistoryModel, ProcedureDS" >
<column name="fullstep_id" />
<column name="fullstep_version" />
</many-to-many>
</list>
<property name="text" column="text" type="String" length="50"/>
<property name="archivedBy" column="archived_by" type="Int32"/>
<property name="archivedDate" column="archived_date" type="DateTime"/>
</class>
</hibernate-mapping>
FullStep
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="Noria.Services.ProcedureDS.FullStepModel, ProcedureDS"
optimistic-lock="version"
table="FullSteps">
<id name="id" column="id" type="Guid" >
<generator class="assigned" />
</id>
<version name="optimisticLock" column="optimistic_lock" type="Int32" />
<property name="text" column="text" type="String" length="50"/>
<list name="dataFields" lazy="false" table="FullSteps_DataFields" cascade="none">
<key column="fullstep_id"/>
<index column="sequence" type="Int32" />
<many-to-many column="datafield_id" class="Noria.Services.ProcedureDS.DataFieldModel, ProcedureDS" />
</list>
</class>
</hibernate-mapping>
DataField
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="Noria.Services.ProcedureDS.DataFieldModel, ProcedureDS"
optimistic-lock="version"
table="DataFields">
<id name="id" column="id" type="Guid" >
<generator class="assigned" />
</id>
<version name="optimisticLock" column="optimistic_lock" type="Int32" />
<property name="name" column="name" type="String" length="50"/>
<property name="description" column="description" type="String" length="50"/>
<property name="datatype" column="datatype_id" type="Byte"/>
<property name="numericMax" column="numeric_max" type="Decimal" />
<property name="numericMin" column="numeric_min" type="Decimal" />
<property name="metricUnitID" column="metric_unit_id" type="Int16" />
</class>
</hibernate-mapping>
Login
FAQ
Search
