Fluent NHibernate Composite Keys

Summary

In a previous blog post, I demonstrated a composite key on a table that was used as a reference between two other tables (a many-to-many example).  This time I’m going to demonstrate the basic mapping of a composite key for a table that will be defined as a class.  This is a one-to-many example, but I’m going to ignore that part of the process and skip right to the meat of the situation.  I did some research on the internet and stumbled across this article: MyWiki: Fluent NHibernate Samples, which has exactly the example I was looking for.

The ERD

I setup a pair of tables with the minimum fields I needed to demonstrate how to do some basic CRUD operations. The ProductId and ProductType fields inside the Product table are a composite primary key. I did not make either key an identity key. I’m going to just put numbers into these fields when I insert data. I would not recommend this practice since two concurrent operations might cause a duplicate key error on insert.

The ProductType Table Class and Mapping

I’m going to show the ProductType table definition first. It’s just a straight mapping of the most basic kind. There is one primary key that is an identity called “Id” and a description string field that is nullable.

public class ProductType
{
    public virtual int Id { get; set; }
    public virtual string Description { get; set; }
}

public class ProductTypeMap : ClassMap<ProductType>
{
    public ProductTypeMap()
    {
        Id(u => u.Id).GeneratedBy.Identity();
        Map(u => u.Description).Nullable();

        Table("sampledata..ProductType");
    }
}

The Product Table Class and Mapping

The tricky table is the product table since it has a composite primary key.

public class Product
{
    public virtual int ProductId { get; set; }
    public virtual int ProductType { get; set; }
    public virtual string Name { get; set; }
        
    public override bool Equals(object obj)
    {
        if (obj == null || GetType() != obj.GetType())
        {
            return false;
        }

        Product that = (Product)obj;

        return this.ProductId == that.ProductId && 
            this.ProductType == that.ProductType;
    }

    public override int GetHashCode()
    {
        return ProductId.GetHashCode() ^ 
            ProductType.GetHashCode();
    }
}

public class ProductMap : ClassMap<Product>
{
    public ProductMap()
    {
        CompositeId()
        .KeyProperty(u => u.ProductId)
        .KeyProperty(u => u.ProductType);

        Map(u => u.Name).Nullable();

        Table("sampledata..Product");
    }
}

The first thing you’ll notice is that there are two overridden methods: The “Equals” method and the “GetHashcode” method. These must be manually handled by your code. Next, the mapping must contain the “CompositeId()” method to define which fields belong to the composite key. This replaced the “Id” mapping method.

The CRUD Code

I wrote a select, an insert and a delete example. My starting data included the following:

The top 4 rows are the Product table and the bottom 4 rows are the product types. When I do my insert, I’m going to intentionally insert a key that contains a Product Id of 1, but a different ProductType key. This will make the composite key unique, but ensure that there is nothing messed up with the primary key being setup as the ProductId.

using (ISession db = MSSQLSessionFactory.OpenSession())
{
    var query =
        (from p in db.Query<Product>()
         join pt in db.Query<ProductType>() on 
            p.ProductType equals pt.Id
         select p).ToList();

    foreach (var item in query)
    {
        Console.WriteLine(item.Name.Trim());
    }
}

// insert new records
using (ISession db = MSSQLSessionFactory.OpenSession())
{
    Product product = new Product()
    {
        ProductId = 1,
        ProductType = 2,
        Name = "Onion"
    };

    db.Save(product);
    db.Flush();
}

// delete one record
using (ISession db = MSSQLSessionFactory.OpenSession())
{
    var product =
        (from p in db.Query<Product>()
         where p.Name == "Onion"
         select p).ToList();

    using (db.BeginTransaction())
    {
        foreach (var item in product)
        {
            db.Delete(item);
        }
        db.Transaction.Commit();
    }
}

If you run this example, you can put a break-point before the delete command and see that the “Onion” product was insert into the product table. Then you can continue and the product will be deleted from the table.

You can download the sample code here:

FluentNhibernateCompositeKey.zip