idbag and fetch join strategy

whoami · **Joined:** Mon Aug 29, 2005 3:07 pm **Posts:** 77

Hibernate version: 1.2.0.4

I have a class which has a collection. I've mapped this collection as an idbag. However, I don't want this collection to be lazy loaded, so I define lazy=false.
I also want NHibernate to execute just one sql statement to retrieve the collection, so I define fetch="join".

My class looks like this:

Code:

public class Project : AuditableEntity<Guid>
{
    public Project()
    {
        ProjectMembers = new List<ProjectMember> ();
    }

     public string Name
     {
         get;
         set;
     }
        
     public IList<ProjectMember> ProjectMembers
     {
         get;
         private set;
     }
}

My mapping file looks like this:

Code:

<id name="Id" column="ProjectId"  >
      <generator class="guid"/>      
    </id>

    <property name="Name" column="Name" />

    <idbag name="ProjectMembers" table="ProjectMembers" cascade="all-delete-orphan" inverse="true" lazy="false" fetch="join">
      <collection-id column="ProjectMemberId" type="Int32">
        <generator class="identity"/>
      </collection-id>
      <key column="ProjectId" />
      <composite-element class="ProjectMember">
        <many-to-one name="PersonInformation" class="User" column="PersonId" fetch="join" />
        <property name="MemberRole" column="RoleId" />
      </composite-element>
    </idbag>

When I retrieve a list of Projects, I see that one SQL statement is issued to retrieve all Projects, and then, for each project, another SQL statement is issued to retrieve the projectmembers for that Project.

How come ? I couldn't find a lot of documentation regarding the idbag ...
Or, does it have something to do with the fact that I use component mapping ?

whoami · **Joined:** Mon Aug 29, 2005 3:07 pm **Posts:** 77

no-one who has an idea ?

whoami · **Joined:** Mon Aug 29, 2005 3:07 pm **Posts:** 77

Is this select n+1 problem a bug in NHibernate, or am I missing something in the documentation ?

whoami · **Joined:** Mon Aug 29, 2005 3:07 pm **Posts:** 77

I've created a test-case for this problem, which you can download here:

klik

In this zip file, you'll find a VS.NET 2008 solution and a directory createdatabasescript which contains a SQL Script-file to create a test-database.
Create a new database in SQL Server 2005, name it 'testcase' and execute the create db script.

Make sure the connection-string in the app.config file is set correctly, and execute the program.
Launch a SQL Server Profiler Trace, click on the button of the program, and see that there are multiple queries fired.

wolli · **Posted:** Thu Jul 31, 2008 9:11 am

How are you retrieving the projects. Can you post the code ?

whoami · **Joined:** Mon Aug 29, 2005 3:07 pm **Posts:** 77

wolli wrote:

How are you retrieving the projects. Can you post the code ?

I'm just using a simple HQL query:

Code:

session.CreateQuery ("from Projects").List<Project>();

You can see it in the sample that i've uploaded.

wolli · **Posted:** Thu Jul 31, 2008 9:17 am

Have you tried:

Code:

session.CreateQuery ("from Projects p inner join fetch p.ProjectMembers").List<Project>();

?

whoami · **Joined:** Mon Aug 29, 2005 3:07 pm **Posts:** 77

No, when I try this, I get an exception which says 'in expected: [hql goes here]'.

Could this mean that I've something wrong in my mapping file ?
Anyway, my ProjectMember class has no property which refers to the Project to which it belongs.
Maybe, this could be a problem ? But, if it is, how do I map this in my hbm.xml file ? I've tried to create a many-to-one which refers to 'Project' within my composite-element , but this gave an error as wel.

whoami · **Joined:** Mon Aug 29, 2005 3:07 pm **Posts:** 77

No, when I try this, I get an exception which says 'in expected: [hql goes here]'.

Could this mean that I've something wrong in my mapping file ?
Anyway, my ProjectMember class has no property which refers to the Project to which it belongs.
Maybe, this could be a problem ? But, if it is, how do I map this in my hbm.xml file ? I've tried to create a many-to-one which refers to 'Project' within my composite-element , but this gave an error as wel.

Offcourse, I also see that I have no real 'class mapping' for my ProjectMember class...
This is done within my Project mapping; it looks like this:

Code:

<idbag name="ProjectMembers" table="ProjectMember" lazy="false" fetch="join">
      <collection-id column="projectmemberid" type="Int32">
        <generator class="identity" />
      </collection-id>

      <key column="ProjectId" />
      <composite-element class="ProjectMember">
        <many-to-one name="UserInformation" class="Person" column="PersonId" fetch="join"/>
        <property name="MemberRole" column="Role" />
      </composite-element>
      
    </idbag>

Note the composite-element tag which has a 'class' attribute