Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Working with legacy databases in NHibernate Part 1 - Compound foreign keys

, 27 Nov 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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 st

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 its better to use surrogate keys or natural keys in data base 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.

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Pete Sutcliffe
Software Developer (Senior) AJ Systems
United Kingdom United Kingdom
I am currently a developer at a management consultancy company.
In addition to all things .NET I have a keen interest in Japan and the Japanese language and hope to be able to combine these passions one day soon.

Comments and Discussions

 
QuestionID as value type PinmemberNikola Radosavljevic26-Nov-12 0:08 
AnswerRe: ID as value type PinmemberPete Sutcliffe26-Nov-12 23:12 
GeneralRe: ID as value type PinmemberNikola Radosavljevic26-Nov-12 23:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 27 Nov 2012
Article Copyright 2012 by Pete Sutcliffe
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid