eager fetching produces 2 queries

Hello I was experimenting with NHibernate tonight and i got little confused with Eager fetching. I created 6 tables with 1-many relation ships 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:

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.Materials1 m3 left join fetch m3.Materials

Generated query is 1 select clause with Left outer joins. This is very satisfactory for me.

Now I will try to set eager fetching in the mapping files:

<set name="Parts" lazy="false" fetch="join">
  <key column="ProductOID"/>
<one-to-many class="Eg.DO.Part, Eg"/>

etc. for all the other tables.

Then I load the product

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

But 3 select clauses are generated:

2010-01-30 10:39:39,319 [10] 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_, materials3_.MaterialOID as Material4_2_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-30 10:39:39,466 [10] DEBUG NHibernate.SQL [(null)] - SELECT materials1x0_.MaterialOID as Material4_1_, materials1x0_.OID as OID1_, materials1x0_.OID as OID2_0_, materials1x0_.Name as Name2_0_, materials1x0_.Price as Price2_0_, materials1x0_.MaterialOID as Material4_2_0_ FROM Materials2 materials1x0_ WHERE materials1x0_.MaterialOID=@p0;@p0 = 1
2010-01-30 10:39:39,476 [10] 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_, materials0_.MaterialOID as Material4_2_0_ FROM Materials2 materials0_ WHERE materials0_.MaterialOID=@p0;@p0 = 1

I tried setting:

 <property name="max_fetch_depth">10</property>

but it doesn't help.

How to eager fetch this object graph for Product not using HQL or criteria API?

ansaurus

tags:

views:

answers:

eager fetching produces 2 queries

related questions