Hi, I am having a strange issue that is probably a simple configuration change, but I can't figure it out. Here is the scenario:
I have 2 objects: an Individual and IndividualAddress, where the Individual contains a collection of IndividualAddress objects. So for example, I have a single individual who has 1 or more addresses.
The relationship is expressed in the hbm as:
<class name="IndividualDTO" mutable="false" table="DCS2000.TBL_SUBR_INDV">
<composite-id>
<key-property name="SubrId">
<column name="SUBR_ID" sql-type="varchar" length="30" not-null="true"/>
</key-property>
<key-property name="IndvId">
<column name="INDV_ID" sql-type="number" length="2" not-null="true"/>
</key-property>
</composite-id>
<property name="SubrId" column="SUBR_ID"/>
<property name="IndvId" column="INDV_ID"/>
<property name="LastName" column="LNME"/>
<property name="FirstName" column="FNME"/>
<property name="DOB" column="DOB"/>
<property name="SSN" column="SSN"/>
<bag name="IndividualAddressList" access="property" table="TBL_SUBR_INDV_ADDR" lazy="false" fetch="join" inverse="true" cascade="all">
<key>
<column name="SUBR_ID"/>
<column name="INDV_ID"/>
</key>
<one-to-many class="IndividualAddressDTO"/>
</bag>
</class>
<class name="IndividualAddressDTO" table="DCS2000.TBL_SUBR_INDV_ADDR">
<composite-id>
<key-property name="SubrId">
<column name="SUBR_ID" sql-type="varchar" length="30" not-null="true"/>
</key-property>
<key-property name="IndvId">
<column name="INDV_ID" sql-type="number" length="2" not-null="true"/>
</key-property>
<key-property name="AddrCode">
<column name="ADDR_CDE" sql-type="number" length="2" not-null="true"/>
</key-property>
</composite-id>
<property name="SubrId" column="SUBR_ID"/>
<property name="IndvId" column="INDV_ID"/>
<property name="Addr1" column="ADDR1"/>
<property name="Addr2" column="ADDR2"/>
<property name="Addr3" column="ADDR3"/>
<property name="City" column="CITY"/>
<property name="State" column="STATE"/>
<property name="Zip" column="ZIP"/>
<property name="CountryCode" column="COUNTRY_CDE"/>
<many-to-one name="Individual" access="property" class="IndividualDTO" lazy="proxy">
<column name="SUBR_ID"/>
<column name="INDV_ID"/>
</many-to-one>
</class>

I originally did not include the fetch attribute in the bag element and I believe it defaults to fetch="select". When I executed the map this way I would get what I would expect object wise; meaning that I would get an Individual with a collection of IndividualAddress objects. In the course of looking at tweaking performance I noticed in the logs that the sql being generated was not doing a join, it was merely selecting the Individual object and then doing an additional select for each IndividualAddress in the collection. So if I had 1 Individual with 3 IndividualAddress objects, 4 sql statements were executed; 1 to get the Individual and 3 to get each IndividualAddress. Looking at time spent, it amounted to some really poor performance.
So after some research (knowing I wanted to do a join), I came across the fetch="join" attribute and implemented that. Now I was getting the single sql statement I wanted for performance, but I was also getting multiple Individual objects when I should only be getting 1. From an sql perspective it made sense; each Individual record would be duplicated for every IndividualAddress record, but I thought NHibernate would be smart enough to eliminate the duplicate records and roll it up into 1 with a collection (as it properly did when the fetch attribute set to sequential select).

Sorry for the long winded question, but what am I doing wrong? I suspect it is something simple, but I have not been able to come up with a work around to this issue. I am concerned that this may be a byproduct of using composite keys or not implementing them correctly. Any help would be greatly appreciated.

thanks in advance

-matt

bump!

Anyone?

That behaviour is by design. If you retrieve the objects with a criteria, you have to add a resultset transformer:

.SetResultTransformer( CriteriaUtil.DistinctRootEntity )

if you use a HQL query, I think a "select distinct ..." does the trick.

Thank you very much, that was exactly what I needed.