Hi,

I'm new to NHiberate and wondering what's the best way to model a typical many-to-many transaction relationship using NHiberate.

The scenario is like an Order, Product and OrderDetails tables. The trouble is that there is no surrogate key on the OrderDetails table. The schma for the three tables look like belows:

Order: OrderID, OrderDate, Client...
Product: ProductID, Name, Desc...
OrderDetails: OrderID, ProductID, Quantity, Discount...

The OrderDetails table has the OrderID and ProductID as composite key.

Can somebody shed some light on this?

Many thanks

From my own experience with many-to-many relationships in your case, it should be better to add an autogenerated field in OrderDetails table and then use this id (e.g. OrderDetailsID) as the entity key.
Then you can establish a one-to-many relationship between Order and OrderDetails and another one-to-many relationship between Product and OrderDetails.
The OrderDetails config xml file should be something like this:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" schema="dbo">
<class name="Namespace.OrderDetails , AssemblyName" table="OrderDetails">
<id name="OrderDetailsID" type="Int32" column="OrderDetailsID" access="nosetter.pascalcase-m-underscore" unsaved-value="0">
<generator class="identity" />
</id>
<many-to-one name="Order" column="OrderID" not-null="true" />
<many-to-one name="Product" column="ProductID" not-null="true" />
<property name="Quantity" column="Quantity" type="Decimal" />
<property name="Discount" column="Discount" type="Decimal" />
( ... )
</class>
</hibernate-mapping>
-------------------------------------
In Order and Product config xml files you should establish one-to-many rellationships to OrderDetails similar to this:
In the Order.hbm.xml file:
<bag name="OrderDetails" inverse="true" lazy="true" cascade="all-delete-orphan">
<key column="OrderID" />
<one-to-many class="Namespace.OrderDetails , AssemblyName" />
</bag>

In the Product.hbm.xml file:
<bag name="OrderDetails" inverse="true" lazy="true" cascade="all-delete-orphan">
<key column="ProductID" />
<one-to-many class="Namespace.OrderDetails , AssemblyName" />
</bag>

Thanks for the help. This surely will work.
There seems to be no other way but to introduce a new identity column just for the NHiberate mapping.