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:
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;
Can someone show me a proper way to map those two classes?
Thanks a lot.
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