Derik Whittaker

Syndication

News


Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Many-to-One Mapping with Composite-ID’s with FluentNhibernate

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:

image

 

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>:
{
	public DiagnosisNonSurgicalMap()
        {
            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 )
                .FetchType.Join();                            
        }
}

We should break down this mapping class just so everyone is on the same page.

  1. First we are setting up our primary key, which we do by using the Id(….) syntax.
  2. Next we are simply mapping our properties in our entity to the columns in the database. 
  3. 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" />:
    <:/id>:
    <:property name="DiagnosisID" column="DIAGNOSIS_ID" type="Int32">:
      <:column name="DIAGNOSIS_ID" />:
    <:/property>:
    <:many-to-one access="field.camelcase-underscore" fetch="join" name="ClinicalDiagnosisInformation">:
      <:column name="DIAGNOSIS_ID" />:
      <:column name="DIAGNOSIS_SET_ID" />:
    <:/many-to-one>:
  <:/class>:
<:/hibernate-mapping>:<:/pre>:

 

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,


Posted 01-22-2009 8:28 PM by Derik Whittaker

[Advertisement]

Comments

DotNetShoutout wrote Many-to-One Mapping with Composite-ID’s with FluentNhibernate
on 01-22-2009 9:38 PM

Thank you for submitting this cool story - Trackback from DotNetShoutout

Reflective Perspective - Chris Alcock » The Morning Brew #271 wrote Reflective Perspective - Chris Alcock &raquo; The Morning Brew #271
on 01-23-2009 3:42 AM

Pingback from  Reflective Perspective - Chris Alcock  &raquo; The Morning Brew #271

Dew Drop - January 23, 2009 | Alvin Ashcraft's Morning Dew wrote Dew Drop - January 23, 2009 | Alvin Ashcraft's Morning Dew
on 01-23-2009 10:37 AM

Pingback from  Dew Drop - January 23, 2009 | Alvin Ashcraft's Morning Dew

Ivan wrote re: Many-to-One Mapping with Composite-ID’s with FluentNhibernate
on 05-04-2009 8:10 AM

What if PTC_Diagnosis referenced _alternative_ key on your C_Diagnosis table? Is there any way to map such a reference?

The problem is quite relevant for me, unfortunately I found no way to tell NHibernate that composite foreign key should reference something other than primary key on 'one' side of the relationship.

Ivan wrote re: Many-to-One Mapping with Composite-ID’s with FluentNhibernate
on 05-04-2009 8:13 AM

wq

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)