RSS

NHibernate mappings for Composite Keys with associations

11 Jul

This example demonstrates how to work on mappings with associations for legacy database tables designed purely using multiple primary keys/composite keys. The article NHibernate and Composite Keys by Anne Epstein provides a thorough explanation on fundamental  composite keys mapping and supporting lazy loading by declaring the composite keys as a class object. Here we only focus on a simple code sample of the associations between entities with composite keys.

Considering 2 sample tables, Products and Orders:

Products:

Name Type
StoreID NUMBER PK
ProductID NUMBER PK
ProductName VARCHAR

Orders:

Name Type
StoreID NUMBER PK
ProductID NUMBER PK
OrderID NUMBER PK
Amount NUMBER


There is no relationship between both tables in the database but apparently the association from Products to Orders is one-to-many.

We define a ProductIdentifier class as the ID of the Product entity class which contains the primary keys, StoreID and ProductID.

In the Product entity class definition we add a ISet collection property of Order entity type. The collection property represents the Order class objects associated with the Product class object. We need to include the Iesi.Collection library which comes with the NHibernate package.

using Iesi.Collections;

[Serializable]
public class ProductIdentifier
{
    public virtual int StoreID { get; set; }
    public virtual int ProductID { get; set; }

    public override bool Equals(object obj)
    {
        if (obj == null)
            return false;
        ProductIdentifier id;
        id = (ProductIdentifier)obj;
        if (id == null)
            return false;
        if (StoreID == id.StoreID && ProductID == id.ProductID)
            return true;
        return false;
    }

    public override int GetHashCode()
    {
        return (StoreID + "|" + ProductID).GetHashCode();
    }
}

public class Product
{
    public virtual ProductIdentifier ProductIdentifier { get; set; }

    public virtual string ProductName { get; set; }

    public virtual ISet Orders { get; set; }

    //public virtual int Version { get; set; }
}

In the Product XML mapping file the ID of Product entity is mapped by <composite-id> element.
The ISet collection property defined previously is mapped by <set> element:

  • The <key> elemens here is like the foreign key of the Orders table, in this case they are StoreID and ProductID.
  • The <one-to-many> element specifies the association.
  • Setting the inverse attribute to true specifies that the collection is a mirror image of the many-to-one association in the Order entity.
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="Product" table="Products" lazy="true" >
    <composite-id name="ProductIdentifier" class="ProductIdentifier">
      <key-property name="StoreID" column="StoreID" />
      <key-property name="ProductID" column="ProductID" />
    </composite-id>
    <!--<version name="Version" column="Version" />-->
    <set name="Orders" inverse="true">
      <key>
        <column name="StoreID"/>
        <column name="ProductID"/>
      </key>
      <one-to-many class="Order"/>
    </set>
    <property name="ProductName" column="ProductName" type="String" />
  </class>
</hibernate-mapping>

The mapping of Orders table is similar to what we just did above. Since it has a many-to-one association with Products, we add a Product class type property in the Order class definition.

[Serializable]
public class OrderIdentifier
{
    public virtual int StoreID { get; set; }
    public virtual int ProductID { get; set; }
    public virtual int OrderID { get; set; }

    public override bool Equals(object obj)
    {
        if (obj == null)
            return false;
        OrderIdentifier id;
        id = (OrderIdentifier)obj;
        if (id == null)
            return false;
        if (StoreID == id.StoreID && ProductID == id.ProductID && OrderID == id.OrderID)
            return true;
        return false;
    }

    public override int GetHashCode()
    {
        return (StoreID + "|" + ProductID + "|" + OrderID).GetHashCode();
    }
}

public class Order
{
    public virtual OrderIdentifier OrderIdentifier { get; set; }

    public virtual int Amount { get; set; }

    public virtual Product Product { get; set; }

    //public virtual int Version { get; set; }
}

In the corresponding mapping file, the <many-to-one>  element specifies the association with Product entity and the <column> elements are the foreign keys of the Orders table referring to the Products table.

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="Order" table="Orders" lazy="true" >
    <composite-id name="OrderIdentifier" class="OrderIdentifier">
      <key-property name="StoreID" column="StoreID" />
      <key-property name="ProductID" column="ProductID" />
      <key-property name="OrderID" column="OrderID" />
    </composite-id>
    <!--<version name="Version" column="Version" />-->
    <many-to-one name="Product" class="Product" insert="false" update="false">
      <column name="StoreID" />
      <column name="ProductID" />
    </many-to-one>
    <property name="Amount" column="Amount" />
  </class>
</hibernate-mapping>

That’s all needed for the association to work. Adding a <version> element in the mapping is recommended for long transactions if adding a new version column of the table in the legacy database is allowed.

Advertisements
 
Leave a comment

Posted by on July 11, 2012 in .NET, C#, NHibernate, ORM

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: