Logo

NHibernate

The object-relational mapper for .NET

Many-to-many relationships with properties

There's a question that seems to appear at least once a month in StackOverflow or the NH users group:

How can I add properties to a many-to-many relationship?

The user of course means adding columns to the "junction" table used to store the many-to-many relationship, and being able to populate them without changing his object model.

That makes some sense from a relational perspective (a table is just a table after all), but not from an OOP one: a relationship does not have properties.

The easiest solution, of course, is to map the relationship as an entity, with regular one-to-many collections from both sides.

This would be the end of it... if it weren't for the fact that it's not what the user wants. If you dig a little further, you'll find that, in most of his use cases, the additional properties don't matter. They are used for auditing purposes, activation/deactivation, etc.

So, how can we code such a model? Answer: using LINQ-to-objects.

Let's consider a typical Users - Roles relationship (a user has many roles, a role is applied by many users).

Step 1: Create the entities

1 public class User 2 { 3 public User() 4 { 5 _UserRoles = new List<UserRole>(); 6 } 7 8 public virtual string Name { get; set; } 9 10 ICollection<UserRole> _UserRoles; 11 protected internal virtual ICollection<UserRole> UserRoles 12 { 13 get { return _UserRoles; } 14 } 15 } 16 17 public class Role 18 { 19 public Role() 20 { 21 _UserRoles = new List<UserRole>(); 22 } 23 24 public virtual string Description { get; set; } 25 26 ICollection<UserRole> _UserRoles; 27 protected internal virtual ICollection<UserRole> UserRoles 28 { 29 get { return _UserRoles; } 30 } 31 } 32 33 public class UserRole 34 { 35 public virtual User User { get; set; } 36 public virtual Role Role { get; set; } 37 public virtual DateTime AssignedDate { get; set; } 38 }

Step 2: Map them (only one side shown; the other is exactly the same)

1 <class name="User"> 2 <id ...>...</id> 3 <property name="Name" /> 4 <bag name="UserRoles" access="nosetter.pascalcase-underscore" 5 inverse="true" cascade="all,delete-orphan"> 6 <key column="UserId" on-delete="cascade" /> 7 <one-to-many class="UserRole" /> 8 </bag> 9 </class> 10 <class name="UserRole"> 11 <id ...>...</id> 12 <many-to-one name="User" /> 13 <many-to-one name="Role" /> 14 <property name="AssignedDate" /> 15 </class>

 

As far as NHibernate is concerned, that is all there is. Now let's make it usable.

Step 3: Add the projection and method (one side shown)

1 public class User 2 { 3 public virtual IEnumerable<Role> Roles 4 { 5 get { return from ur in UserRoles select ur.Role; } 6 } 7 8 public virtual void Add(Role role) 9 { 10 var userRole = new UserRole 11 { 12 User = this, 13 Role = role, 14 AssignedDate = DateTime.Now 15 }; 16 UserRoles.Add(userRole); 17 role.UserRoles.Add(userRole); 18 } 19 20 public virtual void Remove(Role role) 21 { 22 var userRole = UserRoles.Single(r => r.Role == role); 23 UserRoles.Remove(userRole); 24 role.UserRoles.Remove(userRole); 25 } 26 }

VoilĂ ! That's all you need to use it.

Note that I made the UserRoles collection protected internal. If you have code that actually needs to manipulate it, you can expose it.

One small catch: you can't use the Roles projection in queries, because NHibernate knows nothing about it. Still, this should be enough for the expected use cases.


Posted Sun, 26 December 2010 01:52:00 AM by diegose
Filed under:

comments powered by Disqus
© NHibernate Community 2016