In an attempt to provide full disclosure I am a complete NOOBIE when it comes to NHibernate. In fact I have only been using it for a few weeks or so. What this means is take everything I say in this post with a grain of salt cause there may be a better, easier way.
In a previous post I talked about how you can use FluentNhibernate to map to a table which uses a composite-id as its primary key. In this post I wanted to show you how you can create a mapping relationship for a many-to-one that uses a Composite-ID as the mapping key (you can get the info from the NHibernate specs here). Mapping with composite-id’s just feels odd and in fact if I had the ability to I would make changes to our database schema to remove the composite-id, but at this point I do not have the ability to update our schema, so I am stuck with this setup.
Ok, onto the meat of the post. Before we get started we should take a look at the DB schema which I am trying to map to:
As you can see from the circled columns we do in fact have a composite-id scenario. The main table in this setup (C_Diagnosis) which is my one in my many-to-one mapping uses both keys to identify a unique record. I could explain why it is this way, but to be honest it does not really matter too much.
Now that we know what the table schema which we are trying to map to looks like, w should take a look at the mappings needed to setup our relationship.
The mapping we are going to focus on is for the PTC_Diagnosis table (which is the many in my many-to-one mapping), in my particular scenario the entity which is based of the PTC_Diagnosis table as my driver.
public class DiagnosisNonSurgicalMap : ClassMap<:DiagnosisNonSurgical>:
WithTable( "PTC_Diagnosis" );
Id( x =>: x.PTDiagnosisID, "PT_DIAGNOSIS_ID" ).GeneratedBy.Identity();
Map( x =>: x.DiagnosisID, "DIAGNOSIS_ID" );
// removed wasted mappings
References( x =>: x.ClinicalDiagnosisInformation )
.WithColumns( "DIAGNOSIS_ID", "DIAGNOSIS_SET_ID" )
.Access.AsCamelCaseField( Prefix.Underscore )
We should break down this mapping class just so everyone is on the same page.
- First we are setting up our primary key, which we do by using the Id(….) syntax.
- Next we are simply mapping our properties in our entity to the columns in the database.
- Finally, we get to the good stuff which is setting up our many-to-one relationship with the C_Diagnosis table.
When using FluentNHibernate you use the References(…) syntax to setup a many-to-one, and in our case because we have a composite-id relationship we need to use the .WithColumns() method and provide the names of the columns which are our primary keys in the other table. In our example we are using the non-lambda variant of the WithColumns method for simplicity. however, this option is available if you chose to use it.
Now that we have seen how to setup our mapping via the FluentNHibernate library, we should take a look at the resulting XML which is generated.
<:?xml version="1.0" encoding="utf-8"?>:
<:hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="true" assembly="Allscripts.Homecare.OpenAPI.Common" namespace="xxxxx.Entities">:
<:class name="DiagnosisNonSurgical" table="PTC_Diagnosis" xmlns="urn:nhibernate-mapping-2.2">:
<:id name="PTDiagnosisID" column="PT_DIAGNOSIS_ID" type="Int32">:
<:generator class="identity" />:
<:property name="DiagnosisID" column="DIAGNOSIS_ID" type="Int32">:
<:column name="DIAGNOSIS_ID" />:
<:many-to-one access="field.camelcase-underscore" fetch="join" name="ClinicalDiagnosisInformation">:
<:column name="DIAGNOSIS_ID" />:
<:column name="DIAGNOSIS_SET_ID" />:
So there you have it, a fairly simple example on how to setup a many-to-one mapping via composie-id’s.
I would like to mention that doing this with FluentNHibernate was not possible until James Gregory was nice enough to put a patch together for me when I needed it, many thanks to James (and all the other developers) for there excellent work on this project.
Till next time,
01-22-2009 8:28 PM