Lazy loading not working for many-to-one relationship when mapping to a non-key field using property-ref

Question

I have a legacy database that I am mapping using NHibernate. The objects of concern are an Account and a list of Notification objects. The objects look like:

public class Notification
{
    public virtual int Id { get; set; }
    public virtual DateTime BatchDate { get; set; }
    /* other properties */

    public virtual Account Account { get; set; }
}

public class Account 
{
    public virtual int Id { get; set; }
    public virtual string AccountNumber { get; set; }
    /* other properties */ 
}

The mapping files look like:

<class name="Account" table="Account" dynamic-update="true">
<id name="Id" column="AccountID">
    <generator class="native" />
</id>
<property name="AccountNumber" length="15" not-null="true" />
    <!-- other properties -->
</class>

<class name="Notification" table="Notification">
    <id name="Id" column="Id">
        <generator class="native" />
    </id>
    <!-- other properties -->
    <many-to-one name="Account" class="Account" property-ref="AccountNumber" lazy="proxy">
        <column name="AcctNum" />
    </many-to-one>

However, when I create a criteria such as

return session.CreateCriteria(typeof(Notification)).List<Notification>();

I am getting a Select N+1 case where each account is loaded even though the Account is never referenced. Why are all of the accounts getting loaded when the many-to-one is mapped as a lazy proxy?

Answer 1

The issue is caused by the property-ref attribute. Lazy loading only works when the many-to-one reference is using the other object's primary key since NHibernate assumes there's a foreign key constraint enforcing the validity of such a value. With a non-primary key (indicated by the property-ref), NHibernate does not make this assumption and thus does not assume the related object must exist. Since it does not want to create a proxy for an object that does not exist (i.e. should be null instead of a proxy), it eagerly fetches the remote object. This same issue exists when not-found="ignore" is specified since this indicates that the foreign key relationship is not enforced and may result in a null reference.

See also:

• NHibernate creates proxy via session.Load(), but not via Linq or Criteria API
• http://frankmao.com/2007/12/05/lazy-load-conflicts-with-property-ref-in-many-to-one-mapping/