NHibernate - load 100.000 records each with a small lazy-loaded collection

I have an app that has an object e.g. Person with a small list of custom attributes for each person. This collection is loaded lazily through nhibernate and 9/10 times it's only loaded for one person and not for a IList.

This 1/10 times though I need to do an export to excel of all the people in the database and i need to include their custom attributes. So if I am correct about this (and given the mapping below) nhibernate will issue 1 query for 100.000 records and 100.000 queries for their custom attributes.

Is there any way to issue 2 queries - 1 for the records and 1 for all the custom attributes - and have nhibernate create the objects correctly? or maybe just some way to avoid this N+1 problem?

The underlying db is an SQL Server 2008 and the app is a ASP.NET MVC app

Person mapping is

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">   <class name="Core.Person, Core" table="people" optimistic-lock="none">

    <id name="ID" column="id" type="System.Guid" unsaved-value="00000000-0000-0000-0000-000000000000">
      <generator class="guid" />
    </id>
    <many-to-one name="Company" column="company_id" not-null="true" fetch="join" />

    <property name="Name" column="name" type="String" />
    <property name="DoB"开发者_JAVA百科 column="dob" type="DateTime" not-null="true" />

    <map name="CustomFields" lazy="true" table="custom_fields" cascade="all-delete-orphan">
      <key column="person_id" />
      <index column="name" type="System.String" />
      <element column="value" type="System.String" />
    </map>
       </class> </hibernate-mapping>

---

If you execute both queries on the same session the objects should be in the session and NHibernate will check the cache before querying the db, so it will pull the items from the cache.

So execute two queries, one to load the 100 root person objects and one to load all of the child custom attributes objects.

Then do you work with the root object. To verify this works, you can use SQL Server profiler to watch the SQL queries being executed.

---

In order to improve performance for the 2nd event I would recommend 2 things. First of all I would use an IStatelessSession to fetch all of the objects if possible. This will bypass any caching and improve performance.

Most importantly though you need to modify your query and do an eager fetch of the attributes. This way you can get everything in one query. http://www.nhforge.org/doc/nh/en/index.html#querycriteria-dynamicfetching