R – why doesn’t NHibernate FetchMode.Join work in this scenario

nhibernatenhibernate-mapping

I have two entities, C and P.
C is mapped to P in a one-to-one association, with lazy="no-proxy",
like this: (P's mapping:)

<one-to-one name="c" class="C" property-ref="P" access="field" lazy="no-proxy"/>

P is mapped to C in a many-to-one association, like this: (C's mapping:)

<many-to-one name="p" column="PId" class="P" access="field" lazy="no-proxy" not-null="false"/>

usually I use lazy fetching, but in some cases I use FetchMode.Join to avoid the N+1 SELECTs problem, like this:

criteria.SetFetchMode("p", FetchMode.Join)

however, using FetchMode.Join for the C entity performs the query with a left outer join, and then immediately performs N more queries, fetching P's by ID! (profiling courtesy of NHProf)

any idea why this is happening?


answering @KLE's question, here's an excerpt from the documentation here

For a primary key association, add
the following mappings to Employee and
Person respectively:

<one-to-one name="person" class="Person"/>
<one-to-one name="employee" class="Employee" constrained="true"/>

Ensure that the primary keys of the related rows in the PERSON and EMPLOYEE tables are equal.
Alternatively, a foreign key with a
unique constraint, from Employee to
Person, can be expressed as:

<many-to-one name="person" class="Person" column="PERSON_ID" > unique="true"/>

This association can be made
bidirectional by adding the following
to the Person mapping:

<one-to-one name="employee" class="Employee" property-ref="person"/>

please let me know if I misunderstood this. thanks.

Best Answer

Don't know which version of NHibernate you are using but with 2.1.0 - 2.1.2 i've had some issues regarding FetchModes and complex mappings. I hadn't ran into property-ref (which obviously confuses the property walker) but i did have problems with some union classes.

Have you tried setting lazy="true" / "extra" and then define FetchMode.Join ?

Related Topic