NHibernate on a table with two "primary" keys

Question

I'm learning NHibernate in order to layer it over a rather peculiar legacy database. Other applications use the same live database, so I can't make changes that will affect them.

I've run into a problem because one table, which represents hardware devices, has two columns that are used as de facto primary keys. One is the real primary key, an auto-generated row id. The other is a unique and non-null hardware serial number.

Many other tables in the database have a foreign-key relationship with this table. However, some of them use the real primary key - the integer row id - as a foreign key, and some use the hardware id of the device instead.

Note that in practice a hardware ID and row ID, once paired, will remain paired.

Will I be able to create mappings to deal with this in NHibernate, or will I need to create some views to give me a more standardized schema, and use INSTEAD OF triggers to make them updatable?

The DB in use is MSSQL 2000, in case this makes a difference.

Mark Perry · Answer 1 · 2011-07-01T08:30:26.110

In your situation I would do the following:

public class HardwareDevice{
    public virtual int Id {get; set;}
    public virtual string SerialNumber {get; set;}
    //Other stuff
}

public class DomainThingA {
    public virtual int Id {get; set;}
    public virtual HardwareDevice Device {get; set;}
    //Other stuff
}

public class DomainThingB {
    public virtual int Id {get; set;}
    public virtual HardwareDevice Device {get; set;}
    //Other stuff
}

Map out your HardwareDevice class using the AutoGenerated Id as the primary key. My examples use FluentNhibernate for the class maps.

public class HardwareDeviceMap : ClassMap<HardwareDevice> {
    public HardwareDeviceMap(){
        Id(x=>x.Id).GeneratedBy.Native().Column("Id"); //Uses auto number
        Map(x=>x.SerialNumber).Column("SerialNumber");
        //Other mappings
    }
}

Now for mapping out the other two classes:

public class DomainThingAMap : ClassMap<DomainThingA> {
    public DomainThingAMap(){
        Id(x=>x.Id).GeneratedBy.Native(); //Uses auto number
        References(x=>x.Device)
          .Column("DeviceId"); //Joins on Id in HardwareDevice Table by default
        //Other mappings
    }
}

public class DomainThingBMap : ClassMap<DomainThingB> {
    public DomainThingBMap(){
        Id(x=>x.Id).GeneratedBy.Native(); //Uses auto number
        References(x=>x.Device)
           .Column("SerialNumber") //Column in DomainThingB Table
           .PropertyRef("SerialNumber"); //Joins using SerialNumber column (hardware device table)
        //Other mappings
    }
}

The Property-Ref feature of the class maps allows you to join on columns which are not the primary key for these types of legacy database purposes.

NHibernate on a table with two "primary" keys

1 Answers1