I am having a problem with hydrating my child objects. I have a typical parent/children relationship, but when I execute the code below I get too many child records. If I have a parent and it has 3 Tor records and 2 Tee records, I get back 5 of each. I think I understand why it happens (because of the join in the query), but I cannot figure out how to fix it. I am trying to use the ResultTransformers, but can only get it to work for the parent records. Also, rather then having 5 unique child records back, they are all the same record (i.e. All 5 Tor records have the same FirstName and LastName even though the sql returns the correct rows. This has also happened to me when doing a very simple Parent\Child record). Because I am getting back a few thousand parent records I must use some type of join to eliminate trips to the DB.
Hibernate version: 1.2.0.4
Mapping documents:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property">
<class name="Record" table="tblParent" mutable="true">
<composite-id>
<key-property name="Id" column="Instrument_Number"/>
<key-property name="MicrofilmCode" column="Multi_Seq"/>
</composite-id>
<property name ="ModifiedDate" column="LastUpdated_Date"/>
<property name ="SequenceNumber" column="Instrument_Number"/>
<property name ="MicrofilmCode" column="Multi_Seq"/>
<bag name="Tors" inverse="true" lazy="false">
<key>
<column name="Instrument_Number"/>
<column name="Multi_Seq"/>
</key>
<one-to-many class="Tor"/>
</bag>
<bag name="Tees" inverse="true" lazy="false">
<key>
<column name="Instrument_Number"/>
<column name="Multi_Seq"/>
</key>
<one-to-many class="Tee"/>
</bag>
</class>
<class name ="Tor" table="tblChild">
<id name="Id" column="Instrument_Number" access="nosetter.camelcase" unsaved-value="0">
<generator class="assigned"/>
</id>
<property name ="StreetAddress" column="Address1"/>
<property name="FirstName" column="FirstName"/>
<property name="LastName" column="LastName"/>
<property name="UnparsedName" column="Combined_Name"/>
<property name="NonPersonEntityIndicator" column="PC_Flag"/>
<property name="SequenceIdentifier" column="Multi_Seq"/>
</class>
<class name ="Tee" table="tblChild">
<id name="Id" column="Instrument_Number" access="nosetter.camelcase" unsaved-value="0">
<generator class="assigned"/>
</id>
<property name ="StreetAddress" column="Address1"/>
<property name="FirstName" column="FirstName"/>
<property name="LastName" column="LastName"/>
<property name="UnparsedName" column="Combined_Name"/>
<property name="NonPersonEntityIndicator" column="PC_Flag"/>
<property name="SequenceIdentifier" column="Multi_Seq"/>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
ISession session = factory.OpenSession();
IQuery query = session.CreateQuery(job.Query);
ICriteria crit = session.CreateCriteria(typeof(Record));
crit.Add(Expression.Between("ModifiedDate","5/17/2007", "5/18/2007"));
crit.CreateCriteria("Tors", "gr", NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Add(Expression.Sql("gr1_.Name_Type = 'R'")).SetResultTransformer(CriteriaUtil.DistinctRootEntity);
crit.CreateCriteria("Tees", "gre", NHibernate.SqlCommand.JoinType.LeftOuterJoin)
.Add(Expression.Sql("gre2_.Name_Type = 'E'")).SetResultTransformer(CriteriaUtil.DistinctRootEntity);
crit.SetResultTransformer(CriteriaUtil.DistinctRootEntity);
IList<T> test = crit.List<T>();
Name and version of the database you are using:
SQL 2000
The generated SQL (show_sql=true):
exec sp_executesql N'SELECT this_.Instrument_Number as Instrument1_0_2_,
this_.Multi_Seq as Multi2_0_2_,this_.LastUpdated_Date as LastUpda5_0_2_,
gr1_.Instrument_Number as Instrument1___4_, gr1_.Multi_Seq as Multi7___4_,
gr1_.Instrument_Number as Instrument1_4_, gr1_.Instrument_Number as Instrument1_1_0_,
gr1_.Address1 as Address2_1_0_, gr1_.FirstName as FirstName1_0_,
gr1_.LastName as LastName1_0_, gr1_.Combined_Name as Combined5_1_0_,
gr1_.PC_Flag as PC6_1_0_, gr1_.Multi_Seq as Multi7_1_0_,
gre2_.Instrument_Number as Instrument1___5_, gre2_.Multi_Seq as Multi7___5_,
gre2_.Instrument_Number as Instrument1_5_, gre2_.Instrument_Number as Instrument1_1_1_,
gre2_.Address1 as Address2_1_1_, gre2_.FirstName as FirstName1_1_,
gre2_.LastName as LastName1_1_, gre2_.Combined_Name as Combined5_1_1_,
gre2_.PC_Flag as PC6_1_1_, gre2_.Multi_Seq as Multi7_1_1_
FROM tblParent this_
left outer join tblChild gr1_
on this_.Instrument_Number=gr1_.Instrument_Number and
this_.Multi_Seq=gr1_.Multi_Seq
left outer join tblChild gre2_
on this_.Instrument_Number=gre2_.Instrument_Number and
this_.Multi_Seq=gre2_.Multi_Seq
WHERE this_.LastUpdated_Date between @p0 and @p1 and gr1_.Name_Type = ''R'' and gre2_.Name_Type = ''E''', N'@p0 nvarchar(9),@p1 nvarchar(9)', @p0 =
N'5/17/2007', @p1 = N'5/18/2007'
Thank you all for your help
|