Map composite key with unmapped column

Question

How do I map a composite key, if part of it is not mapped in my entity?

Example:

I have a table ITEMDELIVERY containing the columns:

• ITEMDELIVERY_ID (PK)
• DELIVERY_DATE (PK)

I have a table ITEMDELIVERYDETAIL containing the columns:

• ITEMDELIVERYDETAIL_ID (PK)
• ITEMDELIVERY_ID (FK)
• PARTITIONDATE (PK, FK)

As you can see, there is a composite key in both tables and ITEMDELIVERYDETAIL has a "composite" foreign key to ITEMDELIVERY.

There exists no property PartitionDate in my domain model ItemDeliveryDetail that could be mapped to the column PARTITIONDATE (see here and here for the reasons).

But now, how do I map the composite key in ITEMDELIVERYDETAIL?

I tried the following, but that doesn't work:

mapping.CompositeId().KeyProperty(x => x.Id, "ITEMDELIVERYDETAIL_ID")
                     .KeyProperty(x => x.ItemDelivery.DeliveryDate,
                                       "PARTITIONDATE");

I get the following error:

NHibernate.PropertyNotFoundException: Could not find a getter for property 'DeliveryDate' in class 'REM.Domain.NHibernate.ItemDeliveryDetail'

---

UPDATE:

I think I found the solution:

1. Remove the reference to ItemDelivery from the mapping of ItemDeliveryDetail: mapping.References(x => x.ItemDelivery);.Columns("ITEMDELIVERY_ID", "PARTITIONDATE");

2. Change the declaration of the composite key to this:

   mapping.CompositeId().KeyProperty(x => x.Id, "ITEMDELIVERYDETAIL_ID")
                        .KeyReference(x => x.ItemDelivery, "ITEMDELIVERY_ID",
                                                           "PARTITIONDATE");
   

This has the side effect that saving an ItemDeliveryDetail can't cascade save an ItemDelivery. It needs to be saved upfront.

However, I wonder about one thing:
Will this create a PK with three columns? If so, how to avoid it and only create a PK for the two columns needed?

Answer 1

it looks like you have a legacy db and will never generate it with SchemaExport, so it doesnt matter if NH thinks it has 3 PK columns or 1.

after looking at your question Composite key with sequence and if your Id is unique alone then it would be better to

mapping.Id(x => x.Id).GeneratedBy.SequenceIdentity("SQ_TRANSFORM_ITEMDEL_IDDID");
mapping.Reference(x => x.ItemDelivery).Columns("ITEMDELIVERY_ID", "PARTITIONDATE");

even if in the db the PK spans both columns

Update: tricky thing in your example of Invoice. There is a hack to have the second column in the join (as a where which the query optimizer should add to the join)

HasManyToMany(u => u.ItemDeliveryDetails)
    .Table("INVOICEITEM_IDD")
    .ChildWhere("PARTITIONDATE = nhGeneratedAliasForINVOICEITEM_IDD.PARTITIONDATE");