Wednesday, November 9, 2011

NHibernate 3.2 One-To-One Mapping with Foreign Key

Preface


Until recently, I'd been using FluentNhibernate to do my entity mappings in code. Recently, I decided to try the Loquacious (in-code) mapping now part of NHibernate. Since the project was already setup with NHibernate, it the mapping classes had to be converted. This was a (fairly) direct process, except for an oddball mapping we had.


The Problem

Using Loquacious (in code) mapping with NHibernate 3.2, we needed to do a one-to-one mapping of entities where the SQL Server tables had a PK<->FK relationship.

Our Design

Our tables looked like...

User
-----------
UserId (PK)
UserName

UserDetail
--------------------
UserDetailId (PK)
UserId (FK / Unique)
Notes

Our entities looked like...
public class UserInfo
{
    public virtual int Id { get; set; }
    public virtual string UserName { get; set; }
    public virtual UserDetail Details { get; set; }
}

public class UserDetail
{
    public virtual int Id { get; set; }
    public virtual UserInfo User { get; set; }
    public virtual string Notes { get; set; }
}

The important thing to note here is that the User entity does not have a List<Details> property. There will only be one UserDetail for any particular User.

What is a One-To-One Relationship?

A one to one mapping shouldn't use a foreign key. Instead, the tables should both use a synchronized primary key (the pk on both tables are the same value:

UserInfo
-----------
UserInfoId (PK)
UserName

UserDetail
-----------------
UserDetailId (PK/FK)
DetailInfo

The Solution

I failed at using Google to find a solution. There were a few mentions on Stack Overflow. One of the how-to articles on nhforge.org did get me started in the right direction. The problem with the article is that it uses xml mapping (didn't want to do that). It does not one of the major headaches I ran into: that when mapped it would fail to insert the dependent entity.

After poking around for some time, I stumbled on the solution (for our case): it was to reference the foreign key in the dependent entity. Our mapping classes turned out to look like the following:

public sealed class UserInfoMap : ClassMapping
{
    public UserInfoMap()
    {
        Table("UserInfos");

        Id(o => o.Id, map =>
        {
            map.Generator(Generators.Identity);
            map.Column("UserInfoId");
        });

        Property(o => o.UserName);

        OneToOne(o => o.Details,
                 map =>
                 {
                     map.PropertyReference(typeof(UserDetail).GetProperty("User"));
                     map.Cascade(Cascade.All);
                 });
    }
}


public class UserDetailMap : ClassMapping
{
    public UserDetailMap()
    {
        Table("UserDetails");

        Id(o => o.Id, map =>
        {
            map.Generator(Generators.Identity);
            map.Column("UserDetailId");
        });

        Property(o => o.Notes);

        ManyToOne(o => o.User,
                  o =>
                  {
                      o.Column("UserId");
                      o.Unique(true);
                      o.ForeignKey("Users_UserDetails_FK1");
                  });
    }
}

Hope this helps someone else...