NHibernate double fetching for non-existent many-to-one relationship

Question

I have an entity with several Many to One relationships and I've found I can eagerly fetch them in one query like such:

public Accommodation GetEager(int id)
{
    IList<Accommodation> query = NHibernateSession.CurrentFor(NHibernateSession.DefaultFactoryKey)
        .CreateQuery(@"
        select a from Accommodation as a
        left join fetch a.AccommodationType
        left join fetch a.AccommodationUnitType
        left join fetch a.CollectionType
        where a.Id = :id
        ")
        .SetProperties(new {id})
        .SetCacheable(true)
        .List<Accommodation>();

    return query.SingleOrDefault();
}

However, the relationships don't always exist, and I've defined the mappings like so:

mapping.References(x => x.AccommodationUnitType).NotFound.Ignore();

I've found that when a relationship doesn't exist, NHibernate generated a second SQL query looking for it, presumably because it's found that the property is null.

My first question is, how can I prevent this second sql query?

My second question is, is there an easier way to do this fetching into one query? It seems very basic behaviour that one would want to fetch everything in one query rather than a seperate query for each many-to-one relationship (which seems to be the default behaviour).

Answer 1

Are you sure you're using NotFound().Ignore() correctly? This setting determines what NHibernate will do if there is an invalid foreign key. In that case NotFound().Ignore() prevents throwing an EntityNotFoundException. With the setting, if the related object is not found then the value will be null. If you have referential integrity for this relationship then you do not need NotFound().Ignore().

The behavior you're seeing is apparently expected and well known and unlikely to change.

As for your second question, I would advise always starting with the default behavior of lazy loading and only optimizing with eager loads as needed for real world performance problems. Lazy loading is frequently more efficient than eager fetching because a) the object may already be in cache (no db trip required) and b) selecting by primary key is very fast. It's very possible that your query with three joins performs worse than four selects by primary key.