SetFetchMode - not working with lazy loaded collections

colinhumber · **Joined:** Thu Jun 14, 2007 11:26 am **Posts:** 16

I have a 1:m relationship between these 2 tables: Application and Role. In my Application class I have a Roles collection. I have lazy loading turned on by default for the Roles collection but in one instance I want to eagerly load the collection using this code:

ICriteria crit = session.CreateCriteria(typeof(Application));
crit.SetFetchMode("Roles", FetchMode.Select);
IList<Application> user = crit.List<Application>();

The Application is fetched fine but the Roles collection is still being lazy loaded. I profiled the DB calls and the Application is loaded but the Roles are not until I access the collection. What am I doing wrong?

The mapping files follow:

Code:

<hibernate-mapping default-cascade="none"  xmlns="urn:nhibernate-mapping-2.2" assembly="xxx" namespace="xxx">
   <class name="Application" table="Application">
      <id name="ID" type="System.Int32" column="ApplicationId" unsaved-value="0">
         <generator class="identity" />
      </id>
      <!-- Properties -->
      <property name="ApplicationName" type="System.String" column="ApplicationName" not-null="true" length="20" />
      <property name="Title" type="System.String" column="Title" not-null="true" length="50" />
      <property name="Description" type="System.String" column="Description" not-null="true" length="50" />
      <property name="IsPublished" type="System.Boolean" column="IsPublished" not-null="true" />
      <property name="URL" type="System.String" column="URL" not-null="true" length="20" />
      <property name="ModifiedDate" type="System.DateTime" column="ModifiedDate" not-null="true" />
      <property name="ModifiedBy" type="System.String" column="ModifiedBy" not-null="true" length="20" />

      <!-- Relationships -->
      <bag name="Roles" inverse="true" lazy="true" fetch="select" outer-join="false">
         <key column="ApplicationId" />
         <one-to-many class="Role"/>
      </bag>
   </class>
</hibernate-mapping>

Code:

<hibernate-mapping default-cascade="none" xmlns="urn:nhibernate-mapping-2.2" assembly="xxx" namespace="xxx">
   <class name="Role" table="Role">
      <id name="ID" type="System.Int32" column="RoleId" unsaved-value="0">
         <generator class="identity" />
      </id>
      <!-- Properties -->
      <property name="RoleName" type="System.String" column="RoleName" not-null="true" length="20" />
      <property name="Description" type="System.String" column="Description" not-null="true" length="50" />
   
      <!-- Relationships -->
      <!-- m:1 relationship between Role and Application -->
      <many-to-one name="Application" class="Application" lazy="false" fetch="select" column="ApplicationId" />

      <!-- m:n relationship between Role and NewUser -->
      <bag name="NewUsers" table="NewUserRole" inverse="true" lazy="true" fetch="select" outer-join="false">
         <key column="RoleId" />
         <many-to-many class="NewUser" column="NewUserId" />
      </bag>
   </class>
</hibernate-mapping>

colinhumber · **Joined:** Thu Jun 14, 2007 11:26 am **Posts:** 16

If I set the FetchMode to Eager it works fine but does an Outer Join.

karlchu · **Posted:** Tue Jun 19, 2007 5:01 pm

Unless you do not want to get the Applications that do not have any associated Roles, outer join would be the correct type of join to use. An inner join would not retrieve any Application that does not have any associated Role. If that is what you want, you probably should use a different mechanism to achieve that, such as adding another criteria to filter out Applications that do not have Roles.

colinhumber · **Joined:** Thu Jun 14, 2007 11:26 am **Posts:** 16

So I would have to use FetchMode.Eager to eagerly load the related entities? Just out of curiosity, why wouldn't FetchMode.Select work as well, but use a separate query instead of an outer join?

jnapier · **Joined:** Sat Sep 10, 2005 3:46 pm **Posts:** 178

With a criteria query you can either create an alias using criteria.CreateAlias or create a joined criteria with criteria.CreateCriteria. When calling one of these operations you can specify the JoinType. So create an alias for your roles and specify an inner join.

Code:

criteria.CreateAlias("Roles", "r", JoinType.InnerJoin);

you could also do this with HQL.

Code:

from Application a inner join a.roles

karlchu · **Posted:** Tue Jun 19, 2007 7:12 pm

My understanding is that by virtue of specifying FetchMode.Select, you are implying lazy loading. In fact, FetchMode.Select and FetchMode.Lazy have the same integer value (1); whereas FetchMode.Eager and FetchMode.Join have the same value (2). FetchMode.Select and FetchMode.Eager are conflicting concepts.

In practical terms, you could fetch your list of Applications using FetchMode.Select, and then loop through the list and call NHibernate.NHibernateUtil.Initialize(app.Roles) to initialize the Roles collections. To work around the n+1 select problem, you would want to specify a batch size on the Roles collection. Alternatively, fetch your list of Applications using FetchMode.Join. To eliminate the duplicate Applications in the eagerly fetched list, foreach over the list and copy everything over to an ISet or Hashtable to "filter out" the duplicates.

Hope this helps.

colinhumber · **Joined:** Thu Jun 14, 2007 11:26 am **Posts:** 16

OK, I think I'm understanding now. I did see the integer values on Select and Lazy but just didn't clue in there.

If I understand correctly, the NHibernateUtil.Initialize(collectionName) will load the collection's data at that point, removing the need to pass an uninitialized collection back to the UI only to have it be lazy loaded and have a round trip back to the DB.

To combat the N+1 select problem, what is the difference between FetchMode.Eager and FetchMode.Join? If they both have the same integer value, don't they do they same thing?

Thanks for your patience. I'm getting it slowly but surely!

karlchu · **Posted:** Tue Jun 19, 2007 9:10 pm

Your understanding about Initialize() is correct; although, there is still a subsequent database call each time you call Initialize(): i.e. the N+1 problem. Using batch-fetching is one way to alleviate the problem. There is something called "subselect fetching" in NH1.2, but I am still not too sure how to make it happen.

There is a conceptual difference between FetchMode.Eager and FetchMode.Join. The first specify when something is fetched, the second specify how something is fetched. See the first section here:
http://www.hibernate.org/hib_docs/nhibe ... e-fetching

Perhaps I don't have a complete understanding of the difference myself. Rather than misguiding you, I would suggest having a careful read of the above link (I should do the same :-P). Maybe someone else can shed some light on this as well.

Hope this helps.

colinhumber · **Joined:** Thu Jun 14, 2007 11:26 am **Posts:** 16

I think I've got it now. I think you're right in your explanation.

One thing I did notice, which you mentioned in a previous post, is that if I'm using fetch="join" or FetchMode.Eager, I get an IList<NewUser> back that has duplicate entries. You suggested copying everything over to an ISet to filter out the duplicates. That works fine, but I still need to return an IList back to the calling method. Without iterating over the ISet and loading a new IList, how can I get an IList back to the caller?

karlchu · **Posted:** Wed Jun 20, 2007 2:08 pm

If the list of NewUsers are relatively small (less than a few hundred), I personally would just do something like this:

Code:

IList<NewUser> list = criteria.List<NewUser>();
ISet<NewUser> set = new HashedSet<NewUser>(list);
return new List<NewUser>(set);

This is not optimal in terms of performance; but it is easy. In the grand scheme of things, this will not likely be your bottleneck anyway. If your list is huge, you may want to implement your own duplicates-removal algorithm.

karlchu · **Posted:** Wed Jun 20, 2007 2:27 pm

Just saw this in a separate post (http://forum.hibernate.org/viewtopic.ph ... 31#2356231)

Code:

criteria.SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer());