Mapping an extension table that might not have a row

Question

I'm working with Fluent nHibernate on a legacy database and have a main Person table and several extension tables containing additional information about the person. These extension tables are one-to-one, meaning that a person will only have one row on the extension table and the extension table should always map back to one person.

Table: Person
Columns: PersonID, FirstName, LastName, etc.

Table: PersonLogin
Columns: PersonID (FK, unique), UserName, Password, etc.

I have my mappings defined as this (with the irrelevant properties omitted):

public PersonMap()
{
   Table("Person");
   Id(x => x.Id, "PersonID").Not.Nullable();
   References(x => x.Login, "PersonID").LazyLoad();
}

public LoginMap()
{
   Table("PersonLogin");
   Id(x => x.Id, "PersonID").GeneratedBy.Foreign("Person");
   References(x => x.Person, "PersonID").LazyLoad();
}

This works when I have data on both tables, but I recently learned that some of the extension tables don't have data for all Person rows. This caused me to get errors during the query. So, I added .NotFound.Ignore() to my PersonMap making it look like this:

   References(x => x.Login, "PersonID").LazyLoad().NotFound.Ignore();

That caused me to get unnecessary selects from the Login table due to https://nhibernate.jira.com/browse/NH-1001 when my business layer doesn't need to project any of the extension table values. It is causing the performance to be terrible in some of my search queries.

I've scoured a lot of posts, but haven't found a rock solid answer about how to address this scenario. Below are the options I've tried:

Option One:

Create rows on the extension table to ensure there is no Person without a row on the extension table and then remove the .NotFound.Ignore().

The issue with this option is that it's a legacy database and I'm not sure where I'd need to update to ensure that a PersonLogin is inserted when a Person is inserted.

Option Two:

Remove the PersonLogin reference from my PersonMap and custom load it inside my Person class. Like this:

public class Person
{
    /// <summary> Gets or sets the PersonID </summary>
    public virtual int Id { get; set; }

    private bool loadedLogin;
    private PersonLogin login;
    public virtual PersonLogin Login
    {
        get
        {
            if (!loadedLogin)
            {
                login = SessionManager.Session().Get<PersonLogin>(Id);
                loadedLogin = true;
            }

            return login;
        }
        set
        {
             login = value;
             loadedLogin = true;
        }
    }
}

The issue I'm having with it is that I can't eagerly fetch the data when performing a query to pull back a large number of Person objects and their Logins.

Option Three:

I just started playing to see if I could write a custom IEntityNotFoundDelegate to not throw the exception for these objects.

private class CustomEntityNotFoundDelegate : IEntityNotFoundDelegate
{
    public void HandleEntityNotFound(string entityName, object id)
    {
        if (entityName == "my.namespace.PersonLogin")
        {
            return;
        }
        else
        {
            throw new ObjectNotFoundException(id, entityName);
        }
    }
}

And I added this to the config

cfg.EntityNotFoundDelegate = new CustomEntityNotFoundDelegate();

It catches my scenario and returns back now instead of throwing the error, but now when I try to project those PersonLogin properties onto my business objects, it's attempting to use the Proxy object and throws this error that I'm trying to figure out if I can handle cleanly (possibly in a IPostLoadEventListener).

System.Reflection.TargetException occurred
Message = Non-static method requires a target

It looks like you've found a solution now. We try and keep everything in a Q&A form: 1 question and 0 or many answers. I've converted some of your "answers" that look like they're really more information to the question and not an answer, but the last one at least looks like part of it is really part of the answer. It would be great if you could edit this to keep it Q&A style slightly better than I've managed. — Flexo
Thanks @Flexo. I originally wrote it all up together but decided to post the options as separate answers since I thought it provided a better discussion and allowed people to downvote bad options. Anyway, I cleaned up the post and the answer and accepted it. Thank you! — Mark Seefeldt

Mark Seefeldt Mark Seefeldt · Accepted Answer · 2013-03-22T20:59:44

I think I've got this working now by keeping the .NotFound.Ignore().

I originally stated:

That caused me to get unnecessary selects from the Login table due to https://nhibernate.jira.com/browse/NH-1001 when my business layer doesn't need to project any of the extension table values. It is causing the performance to be terrible in some of my search queries.

I was able to tweak my LINQ queries to use the IQueryOver in some instances and to improve my use of LINQ in other scenarios to project only the necessary values. This appears to have resolved the queries from pulling back the extension tables since their values were not needed in the projections.

I thought that my queries weren't projecting these extension tables, but figured out that I had a method ToKeyValuePair that I was using in the projection to concatenate the ID and a Name field together of some related properties. That method was causing the objects to load completely since LINQ wasn't able to determine that the needed fields were present without joining to the extension table.

Mapping an extension table that might not have a row

1 Answers