Eager fetching produces 3 queries instead of 1

filipn · **Joined:** Sat Jan 30, 2010 5:09 am **Posts:** 2

Hello I was experimenting with NHibernate and i got little confused with Eager fetching. I created 6 tables with 1-many relationships and I am trying to eager fetch them in 1 database query. In my example I have Products that consists of Parts that consists of Materials that consists of Materials that consists of Materials that consist of Materials (4 tables for materials because I was out of imagination).

Everything works fine if I use HQL:

Code:

IList<Product> products = session.CreateQuery("from Product p left join fetch p.Parts r left join fetch r.Materials m0 left join fetch m0.Materials m2 left join fetch m2.Materials m3 left join fetch m3.Materials").List<Product>();

2010-01-31 14:45:11,455 [9] DEBUG NHibernate.SQL [(null)] - 
    select
        product0_.OID as OID0_0_,
        parts1_.OID as OID3_1_,
        materials2_.OID as OID1_2_,
        materials3_.OID as OID2_3_,
        materials4_.OID as OID2_4_,
        materials5_.OID as OID2_5_,
        product0_.Name as Name0_0_,
        product0_.Added as Added0_0_,
        parts1_.Name as Name3_1_,
        parts1_.Quantity as Quantity3_1_,
        parts1_.Expiration as Expiration3_1_,
        parts1_.ProductOID as ProductOID3_1_,
        parts1_.ProductOID as ProductOID0__,
        parts1_.OID as OID0__,
        materials2_.Name as Name1_2_,
        materials2_.Price as Price1_2_,
        materials2_.PartOID as PartOID1_2_,
        materials2_.PartOID as PartOID1__,
        materials2_.OID as OID1__,
        materials3_.Name as Name2_3_,
        materials3_.Price as Price2_3_,
        materials3_.MaterialOID as Material4_2__,
        materials3_.OID as OID2__,
        materials4_.Name as Name2_4_,
        materials4_.Price as Price2_4_,
        materials4_.MaterialOID as Material4_3__,
        materials4_.OID as OID3__,
        materials5_.Name as Name2_5_,
        materials5_.Price as Price2_5_,
        materials5_.MaterialOID as Material4_4__,
        materials5_.OID as OID4__ 
    from
        Products product0_ 
    left outer join
        Parts parts1_ 
            on product0_.OID=parts1_.ProductOID 
    left outer join
        Materials materials2_ 
            on parts1_.OID=materials2_.PartOID 
    left outer join
        Materials2 materials3_ 
            on materials2_.OID=materials3_.MaterialOID 
    left outer join
        Materials2 materials4_ 
            on materials3_.OID=materials4_.MaterialOID 
    left outer join
        Materials2 materials5_ 
            on materials4_.OID=materials5_.MaterialOID

But when I try setting eager fetching in mapping files and call:

Code:

  ISession session = sessionFactory.OpenSession();
   Product product = session.Get<Product>(1);

Then i get 3 selects :(

Code:

2010-01-31 14:47:04,698 [9] DEBUG NHibernate.SQL [(null)] - 
    SELECT
        product0_.OID as OID0_3_,
        product0_.Name as Name0_3_,
        product0_.Added as Added0_3_,
        parts1_.ProductOID as ProductOID5_,
        parts1_.OID as OID5_,
        parts1_.OID as OID3_0_,
        parts1_.Name as Name3_0_,
        parts1_.Quantity as Quantity3_0_,
        parts1_.Expiration as Expiration3_0_,
        parts1_.ProductOID as ProductOID3_0_,
        materials2_.PartOID as PartOID6_,
        materials2_.OID as OID6_,
        materials2_.OID as OID1_1_,
        materials2_.Name as Name1_1_,
        materials2_.Price as Price1_1_,
        materials2_.PartOID as PartOID1_1_,
        materials3_.MaterialOID as Material4_7_,
        materials3_.OID as OID7_,
        materials3_.OID as OID2_2_,
        materials3_.Name as Name2_2_,
        materials3_.Price as Price2_2_ 
    FROM
        Products product0_ 
    left outer join
        Parts parts1_ 
            on product0_.OID=parts1_.ProductOID 
    left outer join
        Materials materials2_ 
            on parts1_.OID=materials2_.PartOID 
    left outer join
        Materials2 materials3_ 
            on materials2_.OID=materials3_.MaterialOID 
    WHERE
        product0_.OID=@p0;
    @p0 = 1
2010-01-31 14:47:05,199 [9] DEBUG NHibernate.SQL [(null)] - 
    SELECT
        materials0_.MaterialOID as Material4_1_,
        materials0_.OID as OID1_,
        materials0_.OID as OID2_0_,
        materials0_.Name as Name2_0_,
        materials0_.Price as Price2_0_ 
    FROM
        Materials2 materials0_ 
    WHERE
        materials0_.MaterialOID=@p0;
    @p0 = 1
2010-01-31 14:47:05,238 [9] DEBUG NHibernate.SQL [(null)] - 
    SELECT
        materials0_.MaterialOID as Material4_1_,
        materials0_.OID as OID1_,
        materials0_.OID as OID2_0_,
        materials0_.Name as Name2_0_,
        materials0_.Price as Price2_0_ 
    FROM
        Materials2 materials0_ 
    WHERE
        materials0_.MaterialOID=@p0;
    @p0 = 1

I have tried to set <property name="max_fetch_depth">10</property> and checked again my mapping files but everything looks fine! Any suggestions?

My mapping files:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    namespace="Eg.DO" assembly="Eg">

  <class name="Eg.DO.Product, Eg" table="Products">

    <id name="OID">
      <column name="OID" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="Name"/>
    <property name="Added" />
    
    <set name="Parts" lazy="false" fetch="join">
      <key column="ProductOID"/>
      <one-to-many class="Eg.DO.Part, Eg"/>
    </set>

  </class>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    namespace="Eg.DO" assembly="Eg">

  <class name="Eg.DO.Part, Eg" table="Parts">

    <id name="OID">
      <column name="OID" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="Name"/>
    <property name="Quantity" />
    <property name="Expiration" />
    <many-to-one name="Product"  fetch="join" class="Eg.DO.Product, Eg" column="ProductOID"/>
    <set name="Materials" lazy="false" fetch="join">
      <key column="PartOID"/>
      <one-to-many class="Eg.DO.Material, Eg"/>
    </set>
  </class>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    namespace="Eg.DO" assembly="Eg">

  <class name="Eg.DO.Material, Eg" table="Materials">

    <id name="OID">
      <column name="OID" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="Name"/>
    <property name="Price" />
    <many-to-one name="Part" fetch="join" class="Eg.DO.Part, Eg" column="PartOID"/>
    <set name="Materials" lazy="false" fetch="join">
      <key column="MaterialOID"/>
      <one-to-many class="Eg.DO.Material2, Eg"/>
    </set>
  </class>

  <class name="Eg.DO.Material2, Eg" table="Materials2">

    <id name="OID">
      <column name="OID" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="Name"/>
    <property name="Price" />
    <set name="Materials" lazy="false" fetch="join">
      <key column="MaterialOID"/>
      <one-to-many class="Eg.DO.Material3, Eg"/>
    </set>
  </class>
<!---->
  <class name="Eg.DO.Material3, Eg" table="Materials2">
    <id name="OID">
      <column name="OID" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="Name"/>
    <property name="Price" />
    <set name="Materials" fetch="join" lazy="false" >
      <key column="MaterialOID"/>
      <one-to-many class="Eg.DO.Material4, Eg"/>
    </set>
  </class>

  <class name="Eg.DO.Material4, Eg" table="Materials2">
    <id name="OID">
      <column name="OID" not-null="true"/>
      <generator class="native" />
    </id>
    <property name="Name"/>
    <property name="Price" />
  </class>
</hibernate-mapping>
</hibernate-mapping>
</hibernate-mapping>

nadavsof · **Joined:** Sat Dec 26, 2009 7:05 am **Posts:** 8

it happend to me once, where the nhibernate debug showed me 2 inserts where only one was needed, and when i checked the DB i've noticed that only one of those inserts was actually executed...

so first of all, check the DB (if you're using sql server, its very simple using the profiler tool, for oracle i guess its possible too, but i don't know how because i useally use the DBAs to do this)
to see if all those selects are really running

i didn't look into all of you're mappings, but it seems strange to me to use one to many mapping and set it as lazy with fetch="join"

using fetch = select\sub select is the right way to go with lazy="true" and its hard for me to see any reason why use fetch=join with lazy loading (why would you want to re-load you're root entity everytime you access a proxy collection?)

filipn · **Joined:** Sat Jan 30, 2010 5:09 am **Posts:** 2

nadavsof wrote:

i didn't look into all of you're mappings, but it seems strange to me to use one to many mapping and set it as lazy with fetch="join"

lazy is false!

wolli · **Posted:** Mon Feb 08, 2010 2:25 am

When you use bi-directional associations, you should mark the one-to-many end with inverse="true". I'm not sure if that's the problem here, but it's at least a try. Have you tried using less tables ? Does it work then ?