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...

8 comments:

  1. Hey, this is pretty awesome. I have a slight variant to your situation. Using your example, what if the UserDetail table did not have a identity field but rather used the UserId from the User table as the primary key.

    It would mean the UserDetail entity would only have the UserInfo object and the Notes property.

    How would you map this? I am struggling to figure this one out.

    ReplyDelete
    Replies
    1. If I understand what you're saying, the UserDetail entity would still have an id... It just so happens to be the same as the User's identity. It's also no longer generated by the database (can't use Generators.Identity).

      A lot of times, when you're persisting these things, you have to set the relationship in code. Say you create the User, then create the UserDetail. You have to set the properties of each, so NHib can tell how to persist it. When you do that, you'd also need to set the identity property of the details to that of the User object.

      If that doesn't make sense, I can come up w/ a snippet as an example.

      Delete
    2. So are you saying the UserDetail object will still contain 3 properties: Id, UserInfo object and Notes property, where the Id property and the UserInfo object maps off the same field in the database?

      Delete
    3. A snippet would be helpful, if it is not too much trouble. : )

      Delete
    4. Sure... Let me see what I can do.

      Delete
    5. Can you email me, so we can take this conversation elsewhere?

      Delete
  2. This comment has been removed by the author.

    ReplyDelete