Fetch Strategy Changes The Nr of Records

jfneis · **Posted:** Tue Oct 28, 2008 3:13 pm

Hi.

I'm quite new to NHibernate but after some chating with friends who use Hibernate and a lot of reading over the documentation, I'm still worried about its behavior in one situation.

I'm building a simple domain model with call and call items (a classic parent/child example). After some research, I decided that the best way to implement this was using a one-to-many association. No problem until there.

After that, profiling the DB to discover how queries where performing, I've found that NH was making one select for each CallItem. New search and, voilla!, I've found the fetch-mode property. I simply changed my method to chage it to "join" and the query changed as expected.

Here comes the problem: In my DB, I have two parents (i.e: A and B) and three children (A1, A2 and B1). When I list my criteria using FetchMode = SELECT, NH returns them perfectly: 2 parentes, each one associated with its children (A: A1, A2; B: B1). When I execute it with FetchMode = JOIN, though, the element A is repeated, resulting in a List like: (A: A1, A2; A: A1, A2; B: B1).

I'm using an IList, but even with IESI ISet the problem remains. I also tried to use composite-elements instead of one-to-many association, but the same problem occurs. I also noticed that the methods Equals and GetHashcode of my parent class (Call) are never called, is it normal?

Thank you all in advance. It's really driving me crazy.

José Filipe

Hibernate version: 2.0

Mapping documents:

Code:

   <class name="Call" table="CALL" lazy="false">
      <id name="ID" column="ID_CALL" type="Int64">
         <generator class="identity" />
      </id>
      <property name="StartDate" column= "DT_START" type="DateTime"/>
      <property name="FinishDate" column= "DT_FINISH" type="DateTime"/>
      <set name="CallItems" lazy="false">
         <key column="ID_CALL"/>
         <one-to-many class="CallItem"/>
      </set>
   </class>

   <class name="CallItem" table="CALL_ITEM" lazy="false">
      <id name="ID" column="ID_CALL_ITEM" type="Int64">
         <generator class="identity" />
      </id>
      <property name="StartDate" column= "DT_START" type="DateTime"/>
      <property name="FinishDate" column= "DT_FINISH" type="DateTime"/>
   </class>

Code between sessionFactory.openSession() and session.close():
IList<CallItem> calls = session.CreateCriteria(typeof(Call)).
SetFetchMode("CallItems", FetchMode.Join).
List<CallItem>();

Full stack trace of any exception that occurs:

Name and version of the database you are using: SQL Server 2000

The generated SQL (show_sql=true):

Code:

SELECT this_.ID_CALL as ID1_0_1_, this_.DT_START as DT2_0_1_, this_.DT_FINISH as DT3_0_1_, callitems2_.ID_CALL as ID4_3_, callitems2_.ID_CALL_ITEM as ID1_3_, callitems2_.ID_CALL_ITEM as ID1_1_0_, callitems2_.DT_START as DT2_1_0_, callitems2_.DT_FINISH as DT3_1_0_ FROM CALL this_ left outer join CALL_ITEM callitems2_ on this_.ID_CALL=callitems2_.ID_CALL

Debug level Hibernate log excerpt:

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Para que no se repitan los padres, a la consulta le debes aplicar una transformación del tipo "DISTINCT_ROOT_ENTITY".

Que no se invoque el método de hash en los padres es natural, dado que no están en una colección. Los que están en una colección Hash son los hijos.

jfneis · **Posted:** Wed Oct 29, 2008 9:09 am

Thank you Gonzalo, it solved the problem. The resulting code is:

Code:

            IList<Call> calls = session.CreateCriteria(typeof(Call))
                .SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer())
                .List<Call>();

José Filipe

cremor · **Joined:** Tue Jul 29, 2008 3:30 am **Posts:** 74

You can also use

Code:

.SetResultTransformer(CriteriaUtil.DistinctRootEntity)