Sharing field as composite key and foreign key in Fluent NHibernate

Question

Consider following mapping code please :

public sealed class BankMapping : ClassMap<Bank>
{
    public BankMapping()
    {
        CompositeId()
            .KeyProperty(x => x.SerialNumber, "SerialBankRef")
            .KeyProperty(x => x.FinancialPeriodId, "FinancialPeriodRef");
        Map(x => x.Code);
        Map(x => x.Title);
        Map(x => x.Comment);
        Map(x => x.IsActive);            
        HasMany(x => x.BankBranchs).KeyColumns.Add(new[] { "SerialBankRef", "FinancialPeriodRef" });

    }
}

And the BankBranch mapping code is :

public sealed class BankBranchMapping : ClassMap<BankBranch>
{
    public BankBranchMapping()
    {
       CompositeId()
            .KeyProperty(x => x.FinancialPeriodId, "FinancialPeriodRef")
            .KeyProperty(x => x.SerialNumber, "SerialNumber");
        Map(x => x.Code);
        Map(x => x.Title);
        Map(x => x.Comment);
        Map(x => x.IsActive);
        Map(x => x.Address);
        Map(x => x.Fax);
        Map(x => x.Telephone);
        References(x => x.Bank).Columns(new[] { "SerialBankRef", "FinancialPeriodRef" });
    }
}

As you can see the FinancialPeriodRef field acts as foreign key and part of composite key in the BankBranch mapping and NHibernate builds DB correctly and everything seems to be fine until I try to insert a record in the BankBranch table.

The error System.IndexOutOfRangeException : Invalid index 10 for this SqlParameterCollection with Count=10. which indicates I've mapped a field (FinancialPeriodRef) twice as FK and PK appears and I have no idea how to fix the problem.

I need FinancialPeriodRef in the BankBranch as part of primary key while it's absolutely equal to FinancialPeriodRef from Bank.

I need this field to establish unique constraint and also benefits of composite key is essential.

Not sure exactly what could cause your problem but the `BankBranchMapping` class mapping is probably not what you want. Look at how the `KeyReference` method is used in this [SO question & accepted answer](http://stackoverflow.com/questions/3997114/how-to-map-composite-id-with-fluent-nhibernate-using-an-interface). Using `KeyReference` you can remove the `References(...` from the map. Also, make sure the dependent classes (e.g. `Bank`, etc) have their Id generation set correctly. — Sixto Saez, Feb 21 '13 at 15:13
I used the [KeyReference] instead of Reference but the problem still is not solved. The main cause of the error is that NHibernate can not understand why you want to map a DB field two times. Once as ForeignKey and again as Primarykey. — S.Samani, Feb 27 '13 at 07:14
I am also having this exact problem - did you manage to find a solution? Thanks in advance :] — tom redfern, Aug 14 '14 at 07:59

tom redfern · Answer 1 · 2019-09-12T13:35:15.000

You need to use KeyReference rather than KeyProperty to describe composite foreign keys.

public BankBranchMapping()
{
    CompositeId()
        .KeyReference(x => x.FinancialPeriodId, "FinancialPeriodRef")
        .KeyReference(x => x.SerialNumber, "SerialNumber");
    ...
}

I had the exact same problem as you and after an hour or so came across this post: https://stackoverflow.com/a/7997225/569662 which pointed me right.

Sharing field as composite key and foreign key in Fluent NHibernate

1 Answers1