Fluent Nhibernate composite key mapping

Question

I have tried to figure out this question for quite a long time. I have a hacky way to make it work.

I just want to know if this is possible in Fluent NHibernate mapping.

Say I have two tables for example:

Table ComissionLevel
{
    Year,
    ComissionLevelID,

    ... other properties ....
}
primary key (Year,ComissionLevelID)

Table ClientCommission
{
    Year,
    ClientID,
    CommissionLevelID_1,
    CommissionLevelID_2,

    ... other properties ...
}
primary key (Year,ClientID)
foreign key CommissionLevel1 (Year,CommissionLevelID_1)
foreign key CommissionLevel2 (Year,CommissionLevelID_2)

Currently my mappings are as follow:

public ComissionLevelMap()
{
  Schema("XXXX");
  Table("ComissionLevel");
  LazyLoad();
  CompositeId()
    .KeyProperty(x => x.Year, set => {
        set.ColumnName("Year");
        set.Access.Property(); } )
    .KeyProperty(x => x.CommissionLevelID, set => {
        set.ColumnName("CommissionLevelID");
        set.Length(10);
        set.Access.Property(); } );

  HasMany<ClientCommission>(x => x.ClientCommissions)
    .Access.Property()
    .AsSet()
    .Cascade.AllDeleteOrphan()
    .LazyLoad()
    .Inverse()
    .Generic()
    .KeyColumns.Add("Year", mapping => mapping.Name("Year")
                                                         .SqlType("NUMBER")
                                                         .Nullable())
    .KeyColumns.Add("CommissionLevelID_1", mapping => mapping.Name("CommissionLevelID_1")
                                                         .SqlType("VARCHAR2")
                                                         .Nullable()
                                                         .Length(10));
  HasMany<ClientCommission>(x => x.ClientCommission2s)
    .Access.Property()
    .AsSet()
    .Cascade.AllDeleteOrphan()
    .LazyLoad()
    .Inverse()
    .Generic()
    .KeyColumns.Add("Year", mapping => mapping.Name("Year")
                                                         .SqlType("NUMBER")
                                                         .Nullable())
    .KeyColumns.Add("CommissionLevelID_2", mapping => mapping.Name("CommissionLevelID_2")
                                                         .SqlType("VARCHAR2")
                                                         .Nullable()
                                                         .Length(10));
}

public ClientCommissionMap()
{
  Schema("XXXXX");
  Table("ClientCommission");
  LazyLoad();
  CompositeId()
    .KeyProperty(x => x.ClientID, set => {
        set.ColumnName("ClientID");
        set.Length(10);
        set.Access.Property(); } )
    .KeyProperty(x => x.Year, set => {
        set.ColumnName("Year");
        set.Access.Property(); } );
  References(x => x.ComissionLevel1)
    .Class<ComissionLevel>()
    .Access.Property()
    .Cascade.None()
    .LazyLoad()
    .Insert()
    .Update()
    .Columns("Year", "CommissionLevelID_1");
  References(x => x.ComissionLevel2)
    .Class<ComissionLevel>()
    .Access.Property()
    .Cascade.None()
    .LazyLoad()
    .Insert()
    .Update()
    .Columns("Year", "CommissionLevelID_2");

}

My problem now is whenever I create a CommissionLevel and assign ClientCommission to its collection, if I save them by call session.save(CommissionLevel) it will throw me an exception

<Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index>.

My question here is:

1. Does NHibernate automatically save relationships? like:

       ClientCommission commission = new ClientCommission{Year = 2012, ClientID =SomeGuid};
       CommissionLevel  newCommissionLevel = new CommissionLevel{Year = 2012, CommissionLevelID =NewCommissionLevelGuid};
   
       newCommissionLevel.ClientCommission1s.Add(commission);
       newCommissionLevel.ClientCommission2s.Add(commission);
   
       CommissionLevelRepo.Save(newCommissionLevel);
   

   When I call CommissionLevelRepo.Save(newCommissionLevel), should NHibernate will also update ClientCommission.ComissionLevel1 And ClientCommission.ComissionLevel2

or do I have to say

ClientCommission.ComissionLevel1 = newCommissionLevel; 
ClientCommission.ComissionLevel2 = newCommissionLevel; 

1. For the exception I got, it is because NHibernate doesn't generate correct column, it seems it will generate three Year columns. Cuz if I manually create two property called ComissionLevelID1 and CommissionLevelID2, disable the .Insert() and .Update() on ClientCommission it will save it properly.

Can someone show me a proper way to map those two classes?

Thanks a lot.

Solution

short answer: you can't share columns for multiple references

long answer: NHibernate treats every reference independent of each other but does eliminate duplicate columns in insert statements, hence the references try to access columns which are not present anymore. it does so because if the shared column differs between the to references in the object model, it can't decide which one is correct.

If you can change the database schema and make the ids unique then ignore the year all together in the ids and references.

Update:

you can simplify some of the mappings

CompositeId()
    .KeyProperty(x => x.Year, set => {
        set.ColumnName("Year");
        set.Access.Property(); } )
    .KeyProperty(x => x.CommissionLevelID, set => {
        set.ColumnName("CommissionLevelID");
        set.Length(10);
        set.Access.Property(); } );

// to
CompositeId()
    .KeyProperty(x => x.Year)  // columnname is equal propertyname by default
    .KeyProperty(x => x.CommissionLevelID, set => set.Length(10).Access.Property());  // property is default access and can also be left out


.SqlType("VARCHAR2").Length(10)
// to
.Length(10) or .SqlType("VARCHAR2")
// because length is ignored when sqltype is specified

Comments

[user1494907]

Thanks a lot. Unfortunately I cannot change the db_schema. DBA doesn't allow us to do it. Can I ask where did you read those knowledge about NHibernate? Is there a documentation somewhere?

[Firo]

most of it is painful experience with legacy dbs. I discovered the causes by googling, reading NH source, trial and error. Everytime you encounter such a problem you have to make compromises. Either map it differently (eg ignore primary/foreign keys in db if parts are unique), bleed private properties into the domainmodel, implement nhibernate hooks to get around the default handling of NHibernate.