Hello,

I am using an ICriteria object to query for a list of objects, and they are returned using the List(). However, I noticed that n+1 queries are run (one to get id's and one for every object). Is there a way to optimize this, so just a single query is run that gets all objects at once.

Are the main objects loaded this way ? In that case they probably are lazy-loaded. If it's about some child objects, try criteria.SetFetchMode("path", FetchMode.Join) for that associatione.

Thanks for the reply. I am actually already using joins of the object's mapping sets. I am not using lazy loading, because it would cause issues with my application.

So, I guess what I would like to know if there is a way to do something similar for the top level objects

Can you post the code ?

I don't know if this helps, but I am running the query like this:

IList<RuleBase> rules = crt.Add(NHibernate.Criterion.Expression.In("SegmentId", domainList))
.AddOrder(NHibernate.Criterion.Order.Asc("RuleOrder"))
.SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer())
.List<RuleBase>();

xml:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false">
<class name="RuleBase" table="rules">

<id name="ID" column="id" type="System.Int64" unsaved-value="null">
<generator class="assigned"/>
</id>

<discriminator column="type" type="System.String"></discriminator>
<property name="Name" column="obj_name" type="System.String" length="256" />
<property name="RuleOrder" column="rule_order" type="System.Double"/>
<property name="SegmentId" column="segment_id" type="System.Int64" />

<subclass name="MyRule" discriminator-value="rule">

<!-- mappings -->
<set name="Mapping1" table="map1" fetch="join">
<key column="rule_id"/>
<element column="map_id" type="System.Int64"/>
</set>

OK, i think i figured this one out...

My Rules object has two sets in it that were mapped to the same table. The sets where using a where clause. It looked like it needed to do a second query per object to get the second set.

As far as I can remember, if you have a second association to the same table, you will always get single selects instead of a join, regardless if you have a where clause or not.