NHibernate and Composite Keys

Composite IDs are a common pain point a beginning NHibernate user runs into.  Here's everything you need to get them up and running.

First, a caveat: composite keys are certainly mappable in NHibernate, but it's a little trickier than a typical single identity key would be.  Compared to a normal key, there's some extra setup work, queries are a bit more painful, and they tend to be less optimized in terms of lazy loading. Because of these things, experienced NHibernate users often avoid composite keys entirely when possible.  However,  there are many legacy situations where multiple existing apps all hit the same db-a situation in which, if a composite key is already in place, it’s probably going to have to stay.  As that's the most common use case for composite keys, I'll start from the assumption that you've got an existing database that you can't alter. (this is a *bad thing* - see THIS POST for why, but as developers, those kinds of decisions aren't always under our control)

YOUR OBJECTS

As I mentioned above, if you're considering mapping a composite key, you probably already have a database. (if not,  I’d highly advise an alternative-perhaps sets, perhaps idbags, but that’s for another blog post) The NORMAL, PREFERRED direction of model design would be to work up your classes and once they work the way you want, extract the persistence structure from that (i.e. your DB). But if that were an option for you... well, you probably wouldn't be using a composite key in the first place.  Anyway, let's take a scenario:

Your existing tables:

 

In our brand new NHibernate app, we want to have an object that corresponds to the CategoryProducts idea.  This is a start:

namespace SuperShop.Domain
{
    public class CategoryProduct
    {
        public virtual Product Product { get; set; }
        public virtual Category Category { get; set; }
        public virtual string CustomizedProductDescription { get; set; }

        private DateTime _LastModifiedOn;

        public override bool Equals(object obj)
        {
            if (obj == null)
                return false;
            var t = obj as CategoryProduct;
            if (t == null)
                return false;
            if (Product == t.Product && Category == t.Category)
                return true;
            return false;
        }
        public override int GetHashCode()
        {
            return (Product.SKU + "|" + Category.Name).GetHashCode();
        }
    }
}

So, why the Equals and GetHashcode?  If you try to map a composite key without them, you'll get an NHibernate error stating that they are required.  Here's why: With this two part identifier, NHibernate can't do a simple single id object compare - you need to tell it how to decide equality. Implementing Equals and GetHashcode are always a good idea for anyway so your objects will be have properly in cases like multi-session scenarios where an unsaved object might really be the same as an existing object elsewhere, but in the composite key scenario, not having it is not an option- NHibernate doesn't even *have* a mostly-works technique to fall back on. (Note, this is almost certainly not the most ideal Equals and GetHashcode implementation-take a look here for more on the topic- but hopefully this gives you the general idea. )

MAPPING

A mapping:

<hibernate-mapping>   
<class table="OrderItemProductDetails" name="SuperShop.Domain.ComponentPersonalization, SuperShop.Domain">
    <composite-id>
        <key-many-to-one class="SuperShop.Domain.OrderItemComponent,SuperShop.Domain" name="OrderItemComponent" column="OrderItemProductID" />
        <key-property name="DetailType" column="DetailTypeID" type="SuperShop.Domain.DetailTypes,SuperShop.Domain" />
    </composite-id>
    <version name="LastModifiedOn" column="LastModifiedOn" type="timestamp" access="field.pascalcase-underscore" />
    <property name="DetailValue" column="DetailValue" type="String"></property>
    <property name="DetailCharge" column="DetailCharge" type="Decimal"></property>

</class>
</hibernate-mapping>

Note the <version> element, as well as the matching _LastModifiedOn in the class above. These two items combined let NHibernate know how to tell if an entity is new or not.  In the usual scenario where NHibernate manages the ID, NHibernate monitors whether the id value is the original unsaved value and determines whether to Save or Update for you if you call SaveOrUpdate(), a very handy method.  If NHibernate is not managing the ID, as is the case in an Assigned ID (think an SSN that you manage) or in composite (where you create the relationships or values yourself that make the id)  then it doesn't know how to tell if your id is saved or not-its usual technique doesn't work.  So with <version> NHibernate gets a column it has control over, and can safely monitor for an unsaved value. Without this, you'd be unable to use SaveOrUpdate with this element-you'd have to call Save or Update as appropriate-additionally, since ALL cascading functions on collections are essentially NHibernate calling SaveOrUpdate, you're not going to be able to use cascading. Alternatively, if you don't like the <version> column, you could implement IInterceptor 's IsTransient() method to get similar functionality.   (see documentation at nhforge)

So, if you want to take the <version> approach, you'll need to add a new DateTime column to your CategoryProducts table:

 

QUERYING

An inconvenient aspect of composite ids is the need to query on all parts of the id.  For instance: a GetByID query:


from CategoryProducts c where c.Products = :p and c.Category = :cat


but it's not just on the GetByID, it's *whenever* you might need to search using a particular CategoryProduct. For instance,


select distinct p from ProductImages p join p.CategoryProducts c where c.Products = :p and c.Category = :cat

ID OBJECT

Composite IDs can be problematic for lazy loading... When lazy loading, NHibernate will get just the ids of a collection, and hold off on getting the rest of the object until it's needed. An important fact- NHibernate can't partially load an object-in terms of discrete "things". If you're talking a plain integer ID, it can load up the integer, and then load up the associated object later.  With our object as specified above, the smallest single discrete thing that contains the key is... the whole object- so, you've effectively killed your lazy loading.  What to do if we want lazy loading?  Well, make something smaller that contains the key.  Let's make that ID object:

[Serializable]
public class CategoryProductIdentifier {
        public virtual int ProductId { get; set; }
        public virtual int CategoryId { get; set; }

        public override bool Equals(object obj)
        {
            if (obj == null)
                return false;
            var t = obj as CategoryProductIdentifier;
            if (t == null)
                return false;
            if (ProductId == t.ProductId && CategoryId == t.CategoryId)
                return true;
            return false;
        }
        public override int GetHashCode()
        {
            return (ProductId + "|" + CategoryId).GetHashCode();
        }
}

then, CategoryProduct becomes:

public class CategoryProduct
{
        private CategoryProductIdentifier _categoryProductIdentifier = new CategoryProductIdentifier();
        public virtual CategoryProductIdentifier CategoryProductIdentifier
        {
            get { return _categoryProductIdentifier; }
            set { _categoryProductIdentifier = value; }
        }

        private Product _Product;
        public virtual Product Product
        {
            get { return _Product; }
            set { _Product = value;
                _categoryProductIdentifier.ProductId = _Product.Id; }
        }

        private Category _Category;
        public virtual Category Category
        {
            get { return _Category; }
            set { _Category = value;
                _categoryProductIdentifier.CategoryId = _Category.Id; }
        }
        public virtual string CustomizedProductDescription { get; set; }

}

Mapping Tweaks:

 
<hibernate-mapping>
<class name="CategoryProduct" table="CategoryProducts">
    <composite-id name="CategoryProductIdentifier" class="CategoryProductIdentifier">
        <key-property name="ProductId" column="ProductID" type="Int32" />
        <key-property name="CategoryId" column="CategoryID" type="Int32" />
        <version name="LastModifiedOn" type="timestamp" column="LastModifiedOn" />
    </composite-id>
    <many-to-one name="Product" column="ProductID" class="Product" insert="false" update="false" access="field.pascalcase-underscore" />
    <many-to-one name="Category" column="CategoryID" class="Category" insert="false" update="false" access="field.pascalcase-underscore" />
    <property name="CustomizedProductDescription" column="CustomizedProductDesc" />
</class>
</hibernate-mapping>

The key thing to note here is that Product and Category are referred to twice in both the class and the mapping.  The reason for this is that caching uses primitives like int or string, so we need to feed it something caching-ready.The reason for this is because to index by a custom class in the cache, this composite id class, like an ordinary id, gets serialized.  This serializability comes for free if your id is a single int, but your id is your own custom object, as with this composite id class, you've got to explicitly both specify that serialization is allowed, and make sure the object is valid for serialization.  So we've pulled out those ids as ints into the identifier. However, we still want to be able to traverse these relationships, so we still include the class.  However, if NHibernate tried to update the same db field twice, you'd get errors.  To be able to have both the product relation and the ProductId mapped separately in the same class, we mark the class reference as non-updatable.  Also, note that the Equals and GetHashcode moved to the CategoryProductIdentifier class - the CategoryProduct class is for the most part free of the composite burden; the burden of composite-ness is now on the CategoryProductIdentifier class.

It’s entirely possible I’ve missed something in regards to NHibernate usage with composite keys-if so, let me know, and I’ll add it in.  If I got anything factually wrong, let me know about that too so I can make it right!

EDIT:  Added in serialization info that I'd forgotten. Thanks to bonskijr for letting me know!


Posted 11-20-2009 7:29 PM by Anne Epstein
Filed under:

[Advertisement]

Comments

kibbled_bits wrote re: NHibernate and Composite Keys
on 11-21-2009 1:46 AM

Nice, my only thoughts are that I like adding a surrogate key to cross references tables for a couple of reasons.  One is because I get the uniqueness & avoid a composite key.  Secondly a lot of XREF tables eventually have a table hanging off of it so I only have to propagate the surrogate to the children.

Anne Epstein wrote re: NHibernate and Composite Keys
on 11-21-2009 10:00 AM

kibbled_bits,

Agreed, absolutely-adding in a surrogate key is a preferable idea if that's an option...unfortunately, sometimes, be it the fact that the composite key is *already* being used all over the place as a FK, or, well, politics, you're stuck with the composite.  still...hmm...maybe a post discussing migration is warranted...

kibbled_bits wrote re: NHibernate and Composite Keys
on 11-21-2009 10:26 AM

agreed, I'm "getting" to execute stored procedures now from NHibernate (someone else's DB) much to my dismay

bonskijr wrote re: NHibernate and Composite Keys
on 11-23-2009 3:50 AM

Nice post on composite ID, one thing that was missed is that CompositeID class should also be Serializable(ie marked Serializable attribute)

Anne Epstein wrote re: NHibernate and Composite Keys
on 11-23-2009 9:09 AM

Thanks, bonskijr! Great catch.  I'd set things up for serialization but forgot to actually mark it as such, or put in info about that... oops. done.

walkthewalk wrote re: NHibernate and Composite Keys
on 01-05-2010 12:11 PM

Thanks for a nice clear summary of using Composite Keys.

On question I have...  the NHibernate documentation says:

"You can't use an IIdentifierGenerator to generate composite keys. Instead the application must assign its own identifiers. "

Does anyone know of a way to get NHibernate to generate part of the Composite Key?  In my case, the Composite Key is three integers, two of which are set from the application.  It would be usefule to have NHibernate to auto increment the third.

I'm stuck with a legacy schema and using a SQL identity column is not an option.  Nor is introducing a new primary key.

Anne Epstein wrote re: NHibernate and Composite Keys
on 01-05-2010 5:53 PM

walkthewalk,

Unfortunately, I don't know of a way to get nhibernate to do that.  You might consider asking on the NHibernate users list to see if they have some ideas:  groups.google.com/.../nhusers

Derek wrote re: NHibernate and Composite Keys
on 03-09-2011 5:02 AM

Thanks for posting this (the need for a version tag).

That was getting really! annoying.

Ian wrote re: NHibernate and Composite Keys
on 06-17-2011 3:33 PM

Should we not also override Equals and GetHashCode on the CategoryProduct class?  I assume that overriding on just the CategoryProductIdentifier class probably takes care of it from an NH perspective since it has the mapping info but what about other comparisons?

Anne Epstein wrote re: NHibernate and Composite Keys
on 06-17-2011 6:06 PM

Ian, certainly, it may be a Good Thing to implement Equals and GetHashcode on the CategoryProduct class for reasons unrelated to composite keys. In fact, many people recommend going ahead and implementing Equals and GetHashcode all the time on NHibernate-mapped classes to avoid some not-very-common bugs.  However, unlike on the Identifier class, implementing these methods on CategoryProduct is not required for base functionality, so it's not included above, as I really wanted to give this post a tight focus on the specific changes needed to get composite keys working properly.

spiderman wrote re: NHibernate and Composite Keys
on 03-01-2012 9:28 AM

pls tell wat to do when one of the Composite keys  is foriegn key and the other is an normal attribute

Daniel wrote re: NHibernate and Composite Keys
on 04-08-2012 12:50 PM

Thanks for a great article; it really helped me.  One problem is that with NHibernate 3.1.0.4, I'm getting this error related to my mapping:

The element 'composite-id' in namespace 'urn:nhibernate-mapping-2.2' has invalid child element 'version' in namespace 'urn:nhibernate-mapping-2.2'. List of possible elements expected: 'key-property, key-many-to-one' in namespace 'urn:nhibernate-mapping-2.2'.

Thanks very much!

Anne Epstein wrote re: NHibernate and Composite Keys
on 04-08-2012 2:14 PM

Daniel, If you are going to use the version element, it has to go outside of the composite-id tags, in with your property elements, it should not be inside of your composite-id. Hope that helps!

Daniel wrote re: NHibernate and Composite Keys
on 04-09-2012 9:26 AM

Thanks Anne.  You may want to change your last mapping sample ("Mapping Tweaks") to reflect this.

Zoltan wrote re: NHibernate and Composite Keys
on 04-22-2012 8:25 AM

Hi,

I have an NHibernate mapping problem described on the following link:

stackoverflow.com/.../nhibernate-composite-id-mapping-issue-bi-uni-directional-onetomany-manytoone-a

It's occuring very headache for me, somebody cal help me please?

Thank you,

Zoltan

veasna wrote re: NHibernate and Composite Keys
on 06-21-2012 5:38 AM

    Could you please details what Product and Category POCOs and Hinernate xml mapping should be? I had similar database schemas but I am too new to Nhibernate so I am very far from the correct implimention using these.

Best regards,

Veasna

Rudolf wrote re: NHibernate and Composite Keys
on 10-17-2012 8:47 AM

Hi Anne, nice post!

How do I set this Version in not-xml mappings? I am do mapping by code, because I am using Fluent NHibernate.

rj wrote re: NHibernate and Composite Keys
on 12-18-2013 3:17 PM

Thanks a lot for the article. have been looking all over online to understand why my entity with composite key is not lazy loaded - now I understand since the object itself is the identity.

when I "join fetch" the entity with composite key I expect the join query without any extra selects on composite key table. But it is not working as expected. I see the join query, which is good, but I also see the extra select queries on composite key table. After debugging, I understand it is because of Equals/Gethashcode methods on the entity which is having association to entity with composite key.

department references division. In department's Equals & Gethashcode methods I used "this.Division.Id" and i believe accessing Division property in these methods is creating those extra selects. But the Join query has already run first. I don't understand why I have these extra selects. Can you please help in understanding this behavior

Anne Epstein wrote re: NHibernate and Composite Keys
on 12-18-2013 3:27 PM

rj,

Thanks for the feedback! You should ask your question over at the NHibernate Users google group groups.google.com/forum!forum/nhusers - it would be a good idea to sign up for it anyway if you're using NHibernate, but you should get more responses over there.... (maybe from me, but from many other NH users as well) also, when you post there, if you could include your mappings and relevant class definitions, that would be a big help.

rj wrote re: NHibernate and Composite Keys
on 12-18-2013 4:21 PM

Thank you Anne. will post it there

Milad wrote re: NHibernate and Composite Keys
on 02-24-2014 4:49 PM

Hi Anne,

Thanks for this great article. Unfortunately images are not loading. It may be the expired Google docs url or something like that.

Thanks

Milad

Add a Comment

(required)  
(optional)
(required)  
Remember Me?

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)