FluentNHibernate: Performance Penalty when mapping Reference with NotFound.Ignore()

Question

I use FluentNhibernate and I see NHibernate performing many queries when references of associations are mapped with NotFound.Ignore().

Since the referential integrity of my legacy database is kinda crappy, I'd like to know if there's a workaround or if there's an alternative mapping I can use.

Example:

//no query when loading the entity
References<User>(x => x.User, "UserId").LazyLoad().Nullable();

//performs a hundred queries when I load my entities
References<User>(x => x.User, "UserId").LazyLoad().Nullable().NotFound.Ignore();

Answer 1

This is a known problem unfortunately, there is an issue in NHibernate JIRA (https://nhibernate.jira.com/browse/NH-1001)

There is a workaround though but it isn't pretty. In the Entity you need to do something along the lines of this:

class Entity {

    private int? _userId;

    private User user;

    public User User 
    {
        get { 
            if (_userId == null)
                return null;

            return user;                
        };
        set {
            if (value == null)
                _userId = null;
            else
                _userId = value.UserId;

            _user = value;
        };
    }
 }

And in the mapping you would map the reference as normal but without the not-found = ignore setting but you also map the foreign key field:

 References<User>(Reveal.Membmer<User>("_user"), "UserId").LazyLoad();
 Map(Reveal.Membmer<int?>("_userId")).Nullable().Not.Update().Not.Insert(); // Suppress updates and inserts so that they don't conflict with the mapping of User.

Basically you let NHibernate operate as normal on the _user field and then use the _userId field to manually do the null check. This way you avoid the N+1 selects problem. The downside is that it complicates the Entity and will make queries harder to write. If you want to be able to use the User-property in a LINQ query for example you will have to expose the internal _user field and use that instead.