CODEWITHSUNDEEP
c#nhibernatenhibernate-mappingqueryover
Nanou Ponette
Nanou Ponette

Reputation: 1402

NHibernate QueryOver solution for Select N + 1

I'm having problems with my many-to-many xml mapping. Recently I'm using NHibernate Profiler and discovered this Select N + 1 problem. I can't seem to find a working solution and don't know if my mapping is good or its query related.

Scenario: I have a profile which can have multiple tags that are predefined. Tags can have parents and/or childs. These tags can be used in many profiles. I will show you my relevant classes and mappings.

Account.cs

public class Account
{
    public int Id { get; set; }
    public Profile Profile { get; set; }
}

Profile.cs

public class Profile
{
    public int Id { get; set; }
    public IList<ProfileTag> Tags { get; set; }
}

Profile.hbm.xml

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Agrolink.Application.Models" assembly="Agrolink.Application">
<class name="Agrolink.Application.Models.Profile" lazy="false" table="Profiles" >

<id name="Id" column="Id"  >
  <generator class="identity" />
</id>

<bag name="Tags" table="ProfileTags" cascade="all-delete-orphan" inverse="true">
  <key column="IdProfile" not-null="true"/>
  <one-to-many class="Agrolink.Application.Models.ProfileTag"  />
</bag>

</class>
</hibernate-mapping>

ProfileTag.cs

public class ProfileTag
{
    public int Id { get; set; }

    public Profile Profile { get; set; }

    public Tag Tag { get; set; }
}

ProfileTag.hbm.xml

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Agrolink.Application.Models" assembly="Agrolink.Application">
<class name="Agrolink.Application.Models.ProfileTag" lazy="false" table="ProfileTags" >

<id name="Id" column="Id"  >
  <generator class="identity" />
</id>

<many-to-one name="Profile" class="Agrolink.Application.Models.Profile" column="IdProfile" cascade="save-update" />
<many-to-one name="Tag" class="Agrolink.Application.Models.Tag" column="IdTag" cascade="none" />

</class>
</hibernate-mapping>

Tag.cs

public class Tag
{
    public int Id { get; set; }

    public Tag Parent { get; set; }

    public IList<Tag> Children { get; set; }
}

Tag.hbm.xml

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Agrolink.Application.Models" assembly="Agrolink.Application">
<class name="Agrolink.Application.Models.Tag" lazy="false" table="Tags">

<id name="Id" column="Id"  >
  <generator class="identity" />
</id>

<property name="Name" column="Name" />
<property name="Type"  type="Agrolink.Application.Models.TagType, Agrolink.Application"  column="IdType" />

<many-to-one name="Parent" class="Agrolink.Application.Models.Tag" column="IdParent" cascade="none" />

<bag name="Children" table="Tags" cascade="all" inverse="true">
  <key column="IdParent" not-null="true"/>
  <one-to-many class="Agrolink.Application.Models.Tag"  />
</bag>

</class>
</hibernate-mapping>

When I QueryOver Account I see hes selecting all the tags individually. How can I prevent this? I tried using JoinAlias but it didn't make a difference.

Is my mapping okay with the parent/childs?

All help is appreciated! Thx

Upvotes: 1

Views: 471

Answers (1)

Nanou Ponette
Nanou Ponette

Reputation: 1402

Adding batch-size to the Bag in Tag.hbm.xml worked.

<bag name="Children" table="Tags" cascade="all" inverse="true" batch-size="25">
  <key column="IdParent" not-null="true"/>
  <one-to-many class="Agrolink.Application.Models.Tag"  />
</bag>

Upvotes: 4

Related Questions