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.

Answer 1

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

Answer 2

For composite key, look at Mapping Composite keys in Fluent NHibernate

To simplify mapping, you can change primary key to only one key and create a unique index to represent it, but is not the best solution.

Before (Composite Key):

CREATE TABLE XPTO ( COD_XPTO1 INT NOT NULL IDENTITY,
                    COD_XPTO2 INT NOT NULL,
                    TXT_XPTO VARCHAR(10) NOT NULL)
ALTER TABLE XPTO
  ADD CONSTRAINT PK_XPTO (COD_XPTO1, COD_XPTO2)

After (Single Key with Unique Index):

CREATE TABLE XPTO ( COD_XPTO1 INT NOT NULL IDENTITY,
                    COD_XPTO2 INT NOT NULL,
                    TXT_XPTO VARCHAR(10) NOT NULL)

ALTER TABLE XPTO
  ADD CONSTRAINT PK_XPTO (COD_XPTO1)

CREATE UNIQUE INDEX UK_XPTO ON XPTO (COD_XPTO1, COD_XPTO2)