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: