December 24, 2010

Fluent NHibernate: How to Create Bidirectional Many-to-Many Relationships with Additional Attributes on Relationship Table

Suppose you have a relationship that looks like this in your database:

fluent-nhibernate-complex-many-to-many-mapping

This is a common use-case. In this instance, I wanted to be able to create a table which would log all of the times an individual user read a particular item from an RSS feed. I used an identity key here instead of a composite key so I could take advantage of NHibernate’s auto-magical SaveOrUpdate feature.

Normally with a ManyToMany relationship between two entities in Fluent NHibernate you would simply define a HasManyToMany attribute on either side of the entity, and .Inverse one of them such that only one entity is actually responsible for persisting changes to the database.

In this type of relationship, where your many-to-many relationship actually has additional attributes sitting in the middle of it, you have to define a third type of entity to sit in the middle, and this third entity becomes your relationship table. Here’s what this looks like in code:

The Entities

User – the entity responsible for saving; simplified in order to make it easier to read the code.

public class User
{
    virtual public int UserId { get; set; }
    virtual public string UserName { get; set; }
    virtual public string EmailAddress { get; set; }
    virtual public string Password { get; set; }
    virtual public DateTime DateJoined { get; set; }
	
    virtual public IList<FeedRead> Reads { get; set; }

    public User()
    {
        Reads = new List<FeedRead>();
    }
}

FeedItem – the entity on the other side of the relationship; also simplified.

public class FeedItem
{
    public virtual int FeedItemId { get; set; }
    public virtual Feed Feed { get; set; }
    public virtual string Title { get; set; }
    public virtual DateTime DatePublished { get; set; }
    public virtual Uri OriginalUri { get; set; }

    public virtual IList<FeedRead> Reads { get; set; }

    public FeedItem()
    {
        Reads = new List<FeedRead>();
    }
}

FeedRead – the relationship entity which contains the bidirectional relationships.

public class FeedRead
{
    public virtual int ReadId { get; set; }
    public virtual DateTime TimeRead { get; set; }
    public virtual FeedItem ItemRead { get; set; }
    public virtual User Reader { get; set; }
}

The entities themselves are pretty simple – it’s the mappings that are more interesting.

The Mappings

UserMap – the mapping for our User entity class; the key item here is the HasMany relationship and its attributes. The .Fetch and .Join properties are optional (just a way of telling the NHibernate engine how I want to perform queries against the table on the other side of this relationship,) but everything else is key.

public class UserMap : ClassMap<User>
    {
        public UserMap()
        {
            Table("EasyFeedUser");
            Id(x => x.UserId).GeneratedBy.Identity();
            Map(x => x.UserName)
				.Length(DataConstants.UserNameLength)
				.Unique().Not.Nullable();
            Map(x => x.EmailAddress)
				.Length(DataConstants.EmailAddressLength)
				.Unique().Not.Nullable();
            Map(x => x.DateJoined)
				.Not.Nullable();
            Map(x => x.Password)
				.Length(DataConstants.PasswordHashLength)
				.Not.Nullable();
            HasMany(x => x.Reads)
				.Cascade.AllDeleteOrphan()
				.Fetch.Join()
				.Inverse().KeyColumn("UserId");
        }
    }

FeedItemMap – the mapping for the FeedItem entity class.

public class FeedItemMap : ClassMap<FeedItem>
{
    public FeedItemMap()
    {
        Id(x => x.FeedItemId).GeneratedBy.Identity();
        Map(x => x.OriginalUri)
			.Not.Nullable()
			.Unique()
			.Length(DataConstants.FeedUriLength);
        Map(x => x.DatePublished)
			.Not.Nullable();
        Map(x => x.Title)
			.Not.Nullable()
			.Length(DataConstants.FeedItemTitleLength);
        HasMany(x => x.Reads)
			.Cascade.AllDeleteOrphan()
			.Inverse()
			.Fetch.Join().KeyColumn("FeedItemId");
    }
}

FeedReadMap – the mapping for the FeedRead entity.

public class FeedReadMap : ClassMap<FeedRead>
    {
        public FeedReadMap()
        {
            Table("Reads");
            Id(x => x.ReadId).GeneratedBy.Identity();
            Map(x => x.TimeRead).Not.Nullable();
            References(x => x.Reader)
				.Not.Nullable()
				.Cascade.SaveUpdate()
				.Column("UserId");
            References(x => x.ItemRead)
				.Not.Nullable()
				.Cascade.SaveUpdate()
				.Column("FeedItemId");
        }
    }

So now that I’ve shown you all of the code, what does this mean?

In order to have a bidirectional many-to-many relationship with additional attributes, you need to do the following things:

  1. Create a third entity type which contains all of the columns for the relationship table;
  2. Define the relationships between the two primary entities and the relationship entity (FeedRead) as HasMany.Inverse().Cascade.AllDeleteOrphan and name an explicit foreign key.
  3. Map the relationships of the relationship entity (FeedRead) to the primary entities as References.Cascade.SaveUpdate() and name an explicit column name, which is the same name as the foreign key you defined on the other mappings.

And that’s it – now you can automatically add new FeedRead entities by calling Session.SaveOrUpdate(user) or Session.SaveOrUpdate(feeditem).

Discussion, links, and tweets

I'm the CTO and founder of Petabridge, where I'm making distributed programming for .NET developers easy by working on Akka.NET, Phobos, and more..