FluentHibernate: mapping composite primary keys extended to many levels

Question

I have three tables: BillHead, BillDet, BilDetDet with the following db diagram

PK and FK's are defines as given below:

Table Name     Primary Key                    Foreign Key          FK Table
--------------------------------------------------------------------------------
BillHead         BillNo                            -                  -
BillDet         BillNo, SerialNo                BillNo              BillHead  
BilDetDet    BillNo, SerialNo, DetialSeqNo   BillNo, SerialNo       BillDet   

I have done the mapping and pseudo-code is given below:

//BillHeadMap

public class BillHeadMap : ClassMap<BillHead>
{
    public BillHeadMap()
    {
        Table("BillHead");
        LazyLoad();
        Id(x => x.BillNo).GeneratedBy.Identity().Column("BillNo");
        Map(x => x.CustName).Column("CustName").Not.Nullable().Length(50);
        Map(x => x.BillAmt).Column("BillAmt").Not.Nullable();
        HasMany(x => x.BillDets).KeyColumn("BillNo").Cascade.All().Inverse();
    }
}

//BillDetMap
public class BillDetMap : ClassMap<BillDet>
{
    public BillDetMap()
    {
        Table("BillDet");
        LazyLoad();
        CompositeId()
            .KeyReference(x => x.BillHead, "BillNo")
            .KeyProperty(x => x.SerialNo, "SerialNo");
        Map(x => x.ItemName).Column("ItemName").Not.Nullable().Length(20);
        Map(x => x.ItemAmt).Column("ItemAmt").Not.Nullable();
        HasMany(x => x.BilDetDets).KeyColumns.Add("BillNo", "SerialNo").Cascade.All().Inverse().Table("BilDetDet");
    }
}

//BilDetDetMap
public class BilDetDetMap : ClassMap<BilDetDet>
{
    public BilDetDetMap()
    {
        Table("BilDetDet");
        LazyLoad();
        CompositeId()
            .KeyProperty(x => x.BillNo, "BillNo")
            .KeyProperty(x => x.SerialNo, "SerialNo")
            .KeyProperty(x => x.DetailSeqNo, "DetailSeqNo");
        Map(x => x.DetailAmt).Column("DetailAmt").Not.Nullable();
    }
}  

When I tried to save the Bill using the following code:

using (var sqlTrans = session.BeginTransaction()) {
    BillHead bh = new BillHead() { CustName = "Rama", BillAmt = 50000.00M };
    BillDet bd = new BillDet() { SerialNo = 101, ItemName = "BG", ItemAmt = 50000.00M };
    BilDetDet dd1 = new BilDetDet() { DetailSeqNo = 1001, DetailAmt = 20000.00M };
    BilDetDet dd2 = new BilDetDet() { DetailSeqNo = 1002, DetailAmt = 30000.00M };

    AddFirstLevelBillBetails(bd, dd1, dd2);
    AddSecondBillDetailBill(bh, bd);

    session.Save(bh);
    sqlTrans.Commit();
}


private void AddSecondBillDetailBill(BillHead bh, params BillDet[] bds)
{
    foreach (BillDet bd in bds) {
        bh.AddNewBillDetail(bd);
        bd.BillHead = bh;
    }
}

private void AddFirstLevelBillBetails(BillDet bd, params BilDetDet [] bdds)
{
    foreach (BilDetDet bdd in bdds) {
        bd.AddBillDetail(bdd);
        bdd.BillDet = bd;
    }
}

I'm getting the following error in sqlTrans.Commit() statement.

could not insert: [Sample.CustomerService.Domain.BilDetDet#Sample.CustomerService.Domain.BilDetDet][SQL: INSERT INTO BilDetDet (DetailAmt, BillNo, SerialNo, DetailSeqNo) VALUES (?, ?, ?, ?)]

I have profiled this using sql server profiler and I found out that correct insert statements are being built for BillHead and BillDet tables. When it comes to BilDetDet table the BillNo and SerialNo columns are not assigned any values (they are zero only). I have attached the profiler text:

exec sp_executesql N'INSERT INTO BilDetDet (DetailAmt, BillNo, SerialNo, DetailSeqNo) VALUES (@p0, @p1, @p2, @p3)',N'@p0 decimal(28,5),@p1 int,@p2 int,@p3 
int',@p0=100.21000,@p1=0,@p2=0,@p3=101

Kindly notice that BillNo and SerialNo columns are assigned zero and hence the sql fails.

Please correct the mappings. Thanks in advance.

Answer 1

shouldn't it be:

public BilDetDetMap()
{
    CompositeId()
        .KeyReference(x => x.BillDet, "BillNo", "SerialNo")
        .KeyProperty(x => x.DetailSeqNo, "DetailSeqNo");
}

Update: my source of information

• Intellisense
• mapping compositeKeys

• copy paste your code in a console App with the following code

  var config = Fluently.Configure()
      .Database(SQLiteConfiguration.Standard.InMemory().ShowSql().FormatSql())
      .Mappings(m => m.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly())
      )
      .BuildConfiguration();
  
      var sf = config.BuildSessionFactory();
  
      using (var session = sf.OpenSession())
      {
          new SchemaExport(config).Execute(true, true, false, session.Connection, null);
  
          // copy in test code here
      }
  

:D