NHibernate & Many-to-Many Relationships

By | August 17, 2010

Our next step in our exploration of some of the differences between NHibernate and MyBatis.NET takes us to map composition. In MyBatis.NET, this is a way to reuse defined maps in a DRY way; the post can be found here. As usual, we will be building on the code base we’ve build in this series; refer to the last post in this series for our starting point.

41393436-andy-wilson

Addresses, Addresses, Addresses

Recall the situation in the AdventureWorks database as it relates to addresses by looking at the schema diagram below.

addressSample

This is a typical setup – we have multiple entities referencing an address table via an intermediate junction table. Specifically, we have Vendors, Customers, and Employees all linked to one or more addresses via the VendorAddress, CustomerAddress, and EmployeeAddress tables respectively.

Handling Many-to-Many Relationships With The Bag Tag

In NHibernate, anything you want to hydrate from the database needs to have a mapping defined. For our example, we will build upon the Employee mapping that we’ve built in our series:

<class name="NHAdventureWorksDAL.Person.Employee,NHAdventureWorksDAL"
       table="HumanResources.Employee">
  <id name="Id" column="EmployeeId" />
  <property name="VacationHours" />
  <property name="CurrentMaritalStatus"
            column="MaritalStatus"
            type="NHAdventureWorksDAL.Helpers.MaritalStatusHandler,NHAdventureWorksDAL" />
</class>

And below is the matching Employee class.

public class Employee
{
    public virtual int VacationHours { get; set; }
    public virtual int Id { get; set; }
    public virtual MaritalStatus CurrentMaritalStatus { get; set; }
}

We start by creating a mapping for the Address table, pictured below.

<class name="NHAdventureWorksDAL.Address,NHAdventureWorksDAL"
       table="Person.Address">
  <id name="AddressID" column="AddressID" />
  <property name="Line1" column="AddressLine1" />
  <property name="Line2" column="AddressLine2" />
  <property name="City" column="City" />
  <property name="Zip" column="PostalCode" />
</class>

There’s nothing crazy going on here, it’s just a standard mapping with some of the names changed to match our Address class, which is given below.

public class Address
{
    public virtual int AddressID { get; set; }
    public virtual string Line1 { get; set; }
    public virtual string Line2 { get; set; }
    public virtual string City { get; set; }
    public virtual string State { get; set; }
    public virtual string Zip { get; set; }
}

Aside from syntactic sugar, this is the exact same as the MyBatis.NET case. We start to diverge from the MyBatis.NET example here by augmenting the above Employee class as below.

public class Employee
{
    public virtual int VacationHours { get; set; }
    public virtual int Id { get; set; }
    public virtual MaritalStatus CurrentMaritalStatus { get; set; }
    public virtual IList<Address> Addresses { get; set; }
}

Instead of just one address for our employee, we are going to support multiple addresses, as that is truly what our schema tells us. The only piece we are missing here is the new Employee map. This is where NHibernate’s bag tag comes into play.

<class name="NHAdventureWorksDAL.Person.Employee,NHAdventureWorksDAL"
       table="HumanResources.Employee">
  <id name="Id" column="EmployeeId" />
  <property name="VacationHours" />
  <property name="CurrentMaritalStatus"
            column="MaritalStatus"
            type="NHAdventureWorksDAL.Helpers.MaritalStatusHandler,NHAdventureWorksDAL" />
  <bag name="Addresses"
       table="HumanResources.EmployeeAddress"
       lazy="false">
    <key column="EmployeeID" />
    <many-to-many class="NHAdventureWorksDAL.Address,NHAdventureWorksDAL"
                  column="AddressID" />
  </bag>
</class>

The bag tag is one of the ways NHibernate allows us to specify collection-based properties; the others are set, list, map, array, and primitive-array. For a full treatment of what they mean and their differences, consult the NHibernate docs. We’ll briefly review the contents of the bag tag:

  • name – the name of the collection-based property we are mapping
  • table – the name of the junction table
  • lazy – whether or not we want this collection to be lazy-loaded
  • key element – identifies the column corresponding to the primary key of the parent entity (in this case, the Employee class)
  • many-to-many element – identifies the type that is contained in the collection property, as well as the column in the junction table that serves as the foreign key for this entity (in this case, the  AddressID column, which is a foreign key to the Address table, which corresponds to our Address class

Lastly, we modify our previous employee test (yes, I know, heresy!) to verify that we are actually pulling back the correct address information.

public void CanGetEmployeeByIdTest()
{
    PersonRepo pr = new PersonRepo();

    Employee e = pr.GetEmployeeById(1);

    Assert.That(e, Is.Not.Null);
    Assert.That(e.CurrentMaritalStatus, Is.EqualTo(MaritalStatus.Married));
    Assert.That(e.Addresses, Is.Not.Null);
    Assert.That(e.Addresses.Count, Is.GreaterThan(0));
}

And sure enough, this test passes with no additional modifications to our code.

Conclusions

We land pretty much in the same place we were at once we had concluded the prior post; namely, the approach between NHibernate and MyBatis.NET when it comes to reading in data, defining maps, etc., is very similar. What we gain with NHibernate is a greater sense of meaning from the maps, which is preserved here by virtue of the bag and many-to-many tags in our mapping. Also in keeping with our prior examples, we have avoided the need for any hand-written SQL. For our simple examples, this is a time-saver; this could be a detriment in other situations.

The one thing that really starts making NHibernate pull ahead of MyBatis.NET for general-purpose work is the fact that with a just bit more code and the same amount of mappings, we can also persist our classes to our data store. At this same point on the MyBatis.NET side, we have no such capability without having to define additional maps. The price that we have to pay for the flexibility of MyBatis.NET is starting to grow.