NHibernate Querying across Composite Keys

Error Message: ‘The multi-part identifier “manager1_.MAN_ID” could not be bound’

Firstly, let me say I do not like composite keys. Unique constraints work much better. That said, NHibernate does have support for Composite Keys, for these legacy databases, and it is fairly intuitive with the intellisense. However, I have come across a bizarre issue when querying across these relationships.

Database Diagram

This is the database diagram. Nothing too special about it. Projects have Managers and managers can manager numerous projects.

It is worth saying that the same issue occurs with a two way composite key as well as this three way version. The project roles could probably be ignored since the ‘role’ is stored in the Managers table.

Classes / Mappings

One thing to note, that you have to override the Equals and GetHashCode methods to use with NHibernate.

Project

again some lines have been removed for brevity.

ProjectMember

This is quite interesting example of the composite-id mapping. There are three that make up the key and all are many-to-one elements that map to classes in their own right.  

Manager

Some of the above namespaces have been blurred for client confidentiality.

The Problem

I am currently working on a large search mechanism across various fields of the application. I am basing the search on the blog from Ayende Rahien so I create a DetachedCriteria object for the Project and then everything is based from that.

The method in question is as follows:

A little explanation. criteria (IAdvancedSearchCriteria) is my class which holds the search data. In this case, it is the LineManagerIds pulled from a multiple select box.

So this code runs and is then executed against the current NHSession and using NHProfiler I can review the SQL being generated:

If you remember, this should link projects –> Project Members –> Managers in a Many to Many style situation (although it is mapped as Many to one for the audit information)

Look at the inner SQL and you can see there is no mention of the Managers table. Why? I created an Association to the Manager object. It merely results in a list of errors like ‘The multi-part identifier “manager1_.MAN_ID” could not be bound’.

I assume it is something to do with composite-ids (Did I mention I don’t really like these…)

the association to the Manager is mapped up in the <composite-id> section of the mapping file. Since I have made many searches before, I deduce there is a high probability that this may have something to do with it.

So I assume that NHibernate is not using the Class association in the composite-id section. I could either re-design the database to remove the Composite Id (Tempting… did i mention i hate them?) but my remit is to leave the database alone…

So, to fix the problem, I help NHibernate out. If I add another <property> to the ProjectMembers mapping file for the Manager, I can make the query work.

Now, an important thing to note. I assume, NHiberate will do the inserting and updating based on the <composite-id> mapping, so I have set insert and update = “false” otherwise I think we’d have some confusing errors / SQL statements. I will update this post if this proves incorrect! but it has solved the searching mechanism as shown:

As you can see, we are now joining in on the managers table. There was no change to the code, just the mapping file.