Working with Legacy Databases in NHibernate: Part 1 - Compound Foreign Keys

If there's anything worse than working with legacy code, it's working with Legacy databases. At least with legacy code, if you're lucky, you can ring-fence it off and ignore it when not working on it, but poorly designed databases can infect a whole system and create a situation where your new code still has to interact with it and it's often difficult to fix the schema without having a knock-on effect across other areas (and naturally, there are no tests because it's all legacy...)

Getting legacy databases to play nicely with O/RMs such as NHibernate can be challenging but there are usually ways around any issues if you dig deep enough. In this series of posts, I'll be covering some of the issues I've encountered working with the truly awful database at my old job and how I resolved them. First up: compound keys.

There's a lot of debate over whether it's better to use surrogate keys or natural keys in database design and I'm not going to come down on either side of that argument but in general, compound keys can be difficult to work with in O/RMs so in this scenario, I think surrogate keys are preferred.

Consider the following simplified schema (based loosely on something I had to work on):

The Surveys and Questions tables lend themselves fairly easily to being mapped but SurveyQuestions presents us with a bit of a problem. It's essentially a many-to-many join table but with extra properties that we need to represent the relationship so it will need its own class to map to. It doesn't have an identity of its own though, its primary key is defined by its foreign key relationships to the other two tables.

Fortunately, NHibernate defines a composite-key element that can be used to map compound keys fairly easily. My first attempt at mapping looked something like this:

<class name="SurveyQuestion" table="SurveyQuestions">
    <composite-id>
      <key-many-to-one class="Survey" 
      column="SurveyId" name="Survey" lazy="proxy" />
      <key-many-to-one class="Question" 
      column="QuestionId" name="Question" lazy="proxy" />      
    </composite-id>
    <property name="QuestionNumber" column="QuestionNo" />
</class>

With a corresponding class:

public class SurveyQuestion
{
    public virtual Survey Survey { get; set; }
    public virtual Question Question { get; set; }
    public virtual int QuestionNumber { get; set; }
}

Now, this actually works pretty well and depending on your needs, it may be satisfactory by itself. Interestingly, when I tested this out for this post, it didn't behave quite as I expected. In previous versions of NHibernate, this kind of mapping wouldn't work with lazy loading, resulting in all the entities in the key being loaded up with extra selects immediately but in the latest version, this doesn't seem to be the case and lazy loading works properly. Keep your libraries up-to-date is the lesson there I guess.

A problem comes if you want to query for individual SurveyQuestion objects. Although that's unlikely to be required in this scenario as you'd probably get at them through the parent Survey in other situations, it may be a requirement. The NHibernate ISession object has a Get method that fetches an object by its Id, but as our entity no longer has a single value representing the key, we can't use this method anymore. To work around this, we can move the members representing the key into their own class and make that a property on the SurveyQuestion class.

public class SurveyQuestionId
{
    public virtual Survey Survey { get; set; }
    public virtual Question Question { get; set; }
}

public class SurveyQuestion
{
    public virtual SurveyQuestionId Id { get; set; }
    public virtual int QuestionNumber { get; set; }
}

I've excluded the equality members from the SurveyId class for brevity but these must be overridden or you'll get a run-time exception complaining about their absence The only change necessary to the mapping is to add a class and name attribute to the composite-id element in the mapping. While it's now possible to query for a SurveyQuestion element, it's not very convenient: we need to supply a Survey and Question object to a SurveyQuestionId object to pass in to Get in order to fetch the entity we want. Passing in objects with correct Ids will retrieve the correct SurveyQuestion object...

new SurveyQuestionId { Survey = new Survey { Id = 1 }, Question = new Question { Id = 1 } }

...but the returned object will have its survey and question objects populated from these, i.e., all properties other than the id will be null.

If it's necessary to retrieve this object by id, it's probably easier to only put the Ids on the key object and map the full objects separately:

<class name="SurveyQuestion" table="SurveyQuestions">
    <composite-id class="SurveyQuestionId">
      <key-property name="QuestionId" 
      column="QuestionId"></key-property>
      <key-property name="SurveyId" 
      column="SurveyId"></key-property>
    </composite-id>
    <many-to-one class="Survey" 
    column="SurveyId" name="Survey" lazy="proxy" />
    <many-to-one class="Question" 
    column="QuestionId" name="Question" lazy="proxy" />
    <property name="QuestionNumber" column="QuestionNo" />
  </class>

The class then becomes:

public class SurveyQuestionId
{
    public virtual int SurveyId { get; set; }
    public virtual int QuestionId { get; set; }

    public bool Equals(SurveyQuestionId other)
    {
        return other.SurveyId == SurveyId && other.QuestionId == QuestionId;
    }

    public override bool Equals(object obj)
    {
        if (ReferenceEquals(null, obj)) return false;
        if (ReferenceEquals(this, obj)) return true;
        if (obj.GetType() != typeof(SurveyQuestionId)) return false;
        return Equals((SurveyQuestionId) obj);
    }

    public override int GetHashCode()
    {
        unchecked
        {
            return (SurveyId*397) ^ QuestionId;
        }
    }
}

public class SurveyQuestion : EntityBase<SurveyQuestionId>
{
    public virtual int QuestionNumber { get; set; }

    private Survey _survey;
    public virtual Survey Survey
    {
        get
        {
            return _survey;
        }
        set
        {
            _survey = value;
            Id.SurveyId = value.Id;
        }
    }

    private Question _question;
    protected internal virtual Question Question
    {
        get
        {
            return _question;
        }
        set
        {
            _question = value;
            Id.QuestionId = value.Id;
        }
    }

    public SurveyQuestion()
    {
        Id = new SurveyQuestionId();
    }
}

Objects can then be fetched using a pair of ids only... 

new SurveyQuestionId { QuestionId = 1, SurveyId = 1 }

...and lazy loading is taken care of separately on the main SurveyQuestion class.