nhibernate query with child entities and lazy="false"

Question

Hi All

I have a problem with seperate sql queries being generated for every item in a child collection when selecting the Parent.

I have a Parent object with an IList collection of Child objects.

If I run the following code using linq to nhibernate: IList parents = _repository.ToList();

I get the sql statements like the following:

SELECT * FROM Parent
SELECT * FROM Child WHERE ParentId = 1
SELECT * FROM Child WHERE ParentId = 2
SELECT * FROM Child WHERE ParentId = 3
SELECT * FROM Child WHERE ParentId = 4
etc etc

This is obviously extremely inefficient and assume it must be something to do with the mapping files?

Below is the mapping for the Child collection:

<bag name="Children" lazy="false" table="Child" cascade="all-delete-orphan">
  <key column="ParentId"/>
  <one-to-many class="Child" />
</bag>

Trust this is a newbie mistake somewhere.

Any help greatly appreciate.

S

Answer 1

It all depends on what _repository.ToList() is doing under the covers. You can force "eager" fetching of your collections via HQL syntax similar to the following:

"from Parent inner join fetch Children"

HQL statements are meant to be flexible, and so they tend to ignore your collection mapping and join strategies defined either fluently or in the hbm xml files.

You should now see only a single query executed against the database.

Answer 2

Look at this http://stackoverflow.com/questions/831794/linq-for-nhibernate-and-fetch-mode-of-eager-loading. You can manage your fetch strategy in the mapping file if your subcollection always needs to be in context. However, NH best practice recommends against this. Instead we always keep collections lazy, and set fetching strategies on the queries where appropriate.