NHibernate, each property is filled with a different select statement

Question

I'm retrieving a list of nhibernate entites which have relationships to other tables/entities. I've noticed instead of NHibernate performing JOINS and populating the properties, it retrieves the entity and then calls a select for each property. For example if a user can have many roles and I retrieve a user from the DB, Nhibernate retrieves the user and then populates the roles with another select statement. The problem is that I want to retrieve oh let's say a list of products which have various many-to-many relationships and relationships to items which have their own relationships. In the end I'm left with over a thousand DB calls to retrieve a list of 30 products.

I've also set default lazy loading to false because whenever I save the list of entities to a session, I get an error when trying to retrieve it on another page: LazyInitializationException: could not initialize proxy

If anybody could shed any light I would truly appreciate it.

Here is and example sql:

SELECT   *
FROM     Product b
         LEFT JOIN Minisites mini
           ON b.MinisiteId = mini.MinisiteId
         LEFT JOIN TimeRanges tr
           ON mini.TimeRange = tr.TimeRangeId
WHERE    mini.MinisiteId = 7
          OR tr.FromDate > '3/18/2010 12:00:00 AM'
ORDER BY CASE 
           WHEN mini.MinisiteId = 7 then 1
           else 0
         end
           + CASE 
               WHEN tr.FromDate > '3/18/2010 12:00:00 AM' then 1
               else 0
             end DESC

This is the code that I call to retrieve the product (I pass in an example product to create the query, the actual code of creating the query is too long to post but the query string is basically what I've posted above):

Session.CreateSQLQuery(GetSqlQuery(product)).AddEntity(typeof(Product)).SetResultTransformer(new DistinctRootEntityResultTransformer()).UniqueResult().List<Product>();

From that function I return a list of products which I bind to a datalist. The datalist has rows with the name, company, State and when you click it, it opens to a box underneath with all of the product information, clarity, cut, etc. Each product has relations to to other properties which have relations to others, for example it would have a relation to user which has a relation to address which has a relation to country, etc. For each of these properties a select statement is made.

Here is my hbm file for products:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="IDI.Domain" namespace="IDI.Domain.Entities" default-lazy="false">
    <class name="IDI.Domain.Entities.Product,IDI.Domain" table="Product">
        <id name="ProductId" column="ProductId" type="Int32">
            <generator class="native"></generator>
        </id>    


        <set name="Specialty" generic="true" table="ProductInSpecialties" cascade="save-update" lazy="false">
            <key column="ProductId" not-null="true">
            </key>
            <many-to-many class="IDI.Domain.Entities.Specialty,IDI.Domain" column="SpecialtyId" lazy="false"> </many-to-many>
        </set>

        <!--Search By Rounds -->
        <set name="Clarity"  generic="true" table="ProductInClarity" lazy="false" >
            <key column="ProductId" not-null="true">
            </key>
            <many-to-many class="IDI.Domain.Entities.Clarity,IDI.Domain" column="ClarityId" lazy="false" > </many-to-many>
        </set>

        <set name="Color" generic="true" table="ProductInColor">
            <key column="ProductId" not-null="true">
            </key>
            <many-to-many class="IDI.Domain.Entities.Color,IDI.Domain" column="ColorId" lazy="false"> </many-to-many>
        </set>

        <set name="Carat" generic="true" table="ProductInCarat">
            <key column="ProductId" not-null="true">
            </key>
            <many-to-many class="IDI.Domain.Entities.Carat,IDI.Domain" column="CaratId" lazy="false"> </many-to-many>
        </set>

        <set name="Finish" generic="true" table="ProductInFinish" >
            <key column="ProductId" not-null="true">
            </key>
            <many-to-many class="IDI.Domain.Entities.Finish,IDI.Domain" column="FinishId" lazy="false"> </many-to-many>
        </set>
        <!--End Search By Rounds -->


        <!--Search By Fancy Cut -->
        <set name="FCShape" generic="true" table="ProductInShapes">
            <key column="ProductId" not-null="true">
            </key>
            <many-to-many class="IDI.Domain.Entities.Shape,IDI.Domain" column="ShapeId" lazy="false"> </many-to-many>
        </set>

        <set name="FCClarity" generic="true" table="ProductInFCClarity">
            <key column="ProductId" not-null="true">
            </key>
            <many-to-many class="IDI.Domain.Entities.Clarity,IDI.Domain" column="ClarityId" lazy="false"> </many-to-many>
        </set>

        <set name="FCColor" generic="true" table="ProductInFCColor">
            <key column="ProductId" not-null="true">
            </key>
            <many-to-many class="IDI.Domain.Entities.Color,IDI.Domain" column="ColorId" lazy="false"> </many-to-many>
        </set>

        <set name="FCCarat" generic="true" table="ProductInFCCarat">
            <key column="ProductId" not-null="true">
            </key>
            <many-to-many class="IDI.Domain.Entities.Carat,IDI.Domain"  column="CaratId" lazy="false" > </many-to-many>
        </set>

        <set name="FCFinish" generic="true" table="ProductInFCFinish">
            <key column="ProductId" not-null="true">
            </key>
            <many-to-many class="IDI.Domain.Entities.Finish,IDI.Domain" column="FinishId" lazy="false" > </many-to-many>
        </set>
        <!--End Search By Fancy Cut -->

        <set name="TurnOver" generic="true" table="ProductInTurnOver" lazy="false">
            <key column="ProductId" not-null="true">
            </key>
            <many-to-many class="IDI.Domain.Entities.TurnOver,IDI.Domain" column="TurnOverId" lazy="false"> </many-to-many>
        </set>

        <many-to-one name="User"  column="UserId" not-null="true" cascade="save-update" lazy="false" class="IDI.Domain.Entities.BursaUser,IDI.Domain"></many-to-one>
        <many-to-one name="Minisite"  column="MinisiteId" not-null="false" lazy="false" class="IDI.Domain.Entities.Minisite,IDI.Domain"></many-to-one>

    </class>
</hibernate-mapping>

Thanks.

Eitan

Answer 1

EDIT

Since you're specifying the SQL query yourself, NHibernate has no influence on the query to do any joins.

What you could do is specify a batch-size on the map elements or the class elements of the entities itself (not sure). NHibernate will do some rudimentary optimization and pre-fetch records if it thinks it will help.

---

(old answer)

This is configurable. How, depends on the configuration type (hbm, attributes, fluent). In XML you can specify the fetch attribute: <many-to-one fetch="join" [...] /> The problem is that this may result in a Cartesian product if you're joining in too many relations. It is also possible to use the ICriteria API to set the fetchmode on a per-query basis. If you are fetching so much data, just a thought: is it really necessary to get all the data every time? Are you reporting everything to the user? If not, you could set lazy to false and only fetch the data if it's needed.