NHibernate Fluent many-to-many mapping with multicolumn primary key in junction table

Question

I noticed the DDL that gets generated by Fluent doesn't create PK for the 2 columns that make up the table for many-to-many relationship (i.e. the junction / link / bridge table).

For example (from Example.FirstProject in Fluent source),

Store <-- many-to-many --> Product

In StoreProduct table, you'd have 2 columns:

ProductFK, StoreFK

This StoreProduct table is implicitly generated via Fluent's HasManyToMany statement. I'd like to make it generate DDL that defines the 2 columns as PK.

This is what gets generated by Fluent for SQLite:

create table StoreProduct 
(ProductFK INT not null, 
StoreFK INT not null, 
constraint FKE9A26716DC700501 foreign key (StoreFK) references "Store", 
constraint FKE9A26716815A48A8 foreign key (ProductFK) references "Product");

I'd like it to do this:

create table StoreProduct 
(ProductFK INT not null, 
StoreFK INT not null, 
constraint FKE9A26716DC700501 foreign key (StoreFK) references "Store", 
constraint FKE9A26716815A48A8 foreign key (ProductFK) references "Product",
PRIMARY KEY(ProductFK, StoreFK));

Is that possible with Fluent (i.e. specify in fluent so I get the StoreProduct bridge table to have a PK of both ProductFK and StoreFK)?

Sebastian Edelmeier Sebastian Edelmeier · Accepted Answer · 2012-05-04T12:08:11

Generating a primary key can only be done when you implement StoreProduct as a DataClass of its own, not as an association table. But that way you won't end up having a many-to-many but rather chained many-to-one relations.

NHibernate Fluent many-to-many mapping with multicolumn primary key in junction table

1 Answers