Connecting to Multiple DBs and NHibernate

"Let him who hath understanding
reckon the number of the beast
for it is a human number
its number is one.zero.two"

I just spend the better part of today trying to figure out why NHibernate 1.0.2 was generating incorrect SQL for a query. The situation was like this:

I had a simple class hierarchy consisting on a simple mapping of table "Contacts" with a single on the table ClientContacts. Both tables were matched together with a HistoryId identity column. The Contacts table also contained a uniqueidentitier column called ContactId, but that was not part of any index or constraint whatsoever.

The problem appeared when I started trying to query (using ISession.Find()) for objects of type ClientContactHistory. NHibernate would then generate completely invalid SQL trying to do the join between the two tables based on the "ContactId" column instead of the "HistoryId" one. WTF?

You might think my mapping file was incorrect. Well, no, it wasn't. Here's what my mapping file contained:

<class

   name='N.ClientContactHistoryBase, N'

   polymorphism='explicit'

   mutable='false'

   table='Contacts>

<id name='HistoryId'

    column='HistoryId'

    access='property'>

   <generator class='identity' />

id>

<property name='ChangeDate'/>

<property name='ChangeType'/>

<property name='Username'/>

   <joined-subclass

   name="N.ClientContactHistory, N"

   table="ClientContacts">

   <key column="HistoryId"/>

   <property name='ClientId'/>

joined-subclass>

class>

And this was the SQL NHibernate was generating for the query:

select

      clientcont0_.ContactId as HistoryId,

      clientcont0_.ClientId as ClientId2_,

      [clientcont0__1_].Username as Username1_,

      [clientcont0__1_].ChangeDate as Change2_1_,

      [clientcont0__1_].ChangeType as Change3_1_

from ClientContacts clientcont0_

inner join Contacts_ [clientcont0__1_]

on clientcont0_.ContactId=[clientcont0__1_].HistoryId

Notice how instead of using the HistoryId column I clearly specified as the key for the join, NHibernate simply took that and used it as an alias on top of a different column.

The problem turned out to be the table names themselves. The application I'm working on opens connections to two different databases: one which has the real business data, and one that contains only historic (audit) information. The schemas for both databases are very similar, and we had the same table names on both sides. Apparently, this confuses the heck out of NHibernate, who basically ended up trying to use information it had inferred about the DB schema from the mappings of the business DB and use them to generate the SQL to access the History DB.

The solution? Rename the tables of the History database so that they didn't clash with those in the business DB. Nasty issue.