Hi.
Consider the (in)famous SQL Server example db: Northwind, and its Customer,
Order, OrderDetails and Employee entities. To refresh your memory, here are
their relationships:
Customer.CustomerId* 1:n Order.CustomerId
Employee.EmployeeId* 1:n Order.EmployeeId
Order.OrderId* 1:n OrderDetails.OrderId*
Product.ProductId* 1:n OrderDetails.ProductId*
OrderDetails has a compound PK, which consists of the FK fields OrderId (to
Order.OrderId) and ProductId (to Product.ProductId)
The two m:1 relationships of Order to Customer and Employee define an
objectified relationship (m:n with a non-pure intermediate) between Customer
and Employee.
One can also define a m:n between Order and Product (over OrderDetails).
Say I do both, so both the m:n between customer and employee and the m:n
between order and product.
If do that, I run into a strange problem with '<key>' in the <set>
definition for the m:n collection for the Customer: it has to specify a
non-pk field in the intermediate table (Order.Employee) however NHibernate
thinks it's redefining a key and this results in the error:
Unhandled Exception: System.TypeInitializationException: The type
initializer for 'NW.SessionManager' threw an exception. ---> NHibernate.FK
UnmatchingColumnsException: Foreign key (FK549B840DB6D7C729:[dbo].[Order
Details] [OrderID])) must have same number of columns as the referenced
primary key ([dbo].[Orders] [EmployeeID, CustomerID])
at NHibernate.Cfg.Configuration.LogAndThrow(Exception exception)
at NHibernate.Cfg.Configuration.SecondPassCompileForeignKeys(Table table,
ISet done)
at NHibernate.Cfg.Configuration.SecondPassCompile()
at NHibernate.Cfg.Configuration.BuildSessionFactory()
at NW.SessionManager..cctor() in ...
As you can see, it thinks the PK of Order is [EmployeeID, CustomerID]. But
it's defined as 'OrderId' in its mapping (see below).
I looked at the documentation for <set> but I can't figure out how to
specify a non-key field for the intermediate table, so in the case of the
Customer.Employees property:
<set name="Employees" access="field.camelcase-underscore"
table="[dbo].[Orders]" cascade="all-delete-orphan" fetch="select">
<key>
<column name="CustomerID"/>
</key>
<many-to-many class="Employee">
<column name="EmployeeID"/>
</many-to-many>
</set>
I have to specify the 'CustomerID' field as the 'key', how else do I specify
the first FK field in the intermediate table (for the first FK constraint) ?
IMHO this is the only way.
Sooooooo, long story, but in short: is this supported? Or does the FK field
has to be a PK field as well (like the case with OrderDetails, where the
OrderID is in the PK, and I used a key-many-to-one to map it) ?
Mappings: (irrelevant goo is cut)
===============
Customer:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NorthwindNH.Model"
namespace="NW.EntityClasses">
<class name="Customer" table="[dbo].[Customers]"
dynamic-update="true" dynamic-insert="true" lazy="false"
optimistic-lock="none" >
<id name="CustomerId" column="CustomerID"
access="field.camelcase-underscore" >
<generator class="assigned"/>
</id>
... fields cut
<set name="Employees" access="field.camelcase-underscore"
table="[dbo].[Orders]" cascade="all-delete-orphan" fetch="select">
<key>
<column name="CustomerID"/>
</key>
<many-to-many class="Employee">
<column name="EmployeeID"/>
</many-to-many>
</set>
<set name="Orders" access="field.camelcase-underscore"
cascade="all-delete-orphan" inverse="true" fetch="select">
<key>
<column name="CustomerID"/>
</key>
<one-to-many class="Order"/>
</set>
</class>
</hibernate-mapping>
Employee:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NorthwindNH.Model"
namespace="NW.EntityClasses">
<class name="Employee" table="[dbo].[Employees]"
optimistic-lock="none" >
<id name="EmployeeId" column="EmployeeID"
access="field.camelcase-underscore" >
<generator class="identity"/>
</id>
.. fields cut
<set name="Customers" access="field.camelcase-underscore"
table="[dbo].[Orders]" cascade="all-delete-orphan" fetch="select">
<key>
<column name="EmployeeID"/>
</key>
<many-to-many class="Customer">
<column name="CustomerID"/>
</many-to-many>
</set>
...
<set name="Orders" access="field.camelcase-underscore"
cascade="all-delete-orphan" inverse="true" fetch="select">
<key>
<column name="EmployeeID"/>
</key>
<one-to-many class="Order"/>
</set>
...
</class>
</hibernate-mapping>
Order:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NorthwindNH.Model"
namespace="NW.EntityClasses">
<class name="Order" table="[dbo].[Orders]" optimistic-lock="none" >
<id name="OrderId" column="OrderID"
access="field.camelcase-underscore" >
<generator class="identity"/>
</id>
.. fields cut
<many-to-one name="Customer"
access="field.camelcase-underscore" fetch="select" cascade="all">
<column name="CustomerID"/>
</many-to-one>
<many-to-one name="Employee"
access="field.camelcase-underscore" fetch="select" cascade="all">
<column name="EmployeeID"/>
</many-to-one>
<set name="OrderDetails" access="field.camelcase-underscore"
cascade="all-delete-orphan" inverse="true" fetch="select">
<key>
<column name="OrderID"/>
</key>
<one-to-many class="OrderDetail"/>
</set>
<set name="Products" access="field.camelcase-underscore"
table="[dbo].[Order Details]" cascade="all-delete-orphan" fetch="select">
<key>
<column name="OrderID"/>
</key>
<many-to-many class="Product">
<column name="ProductID"/>
</many-to-many>
</set>
...
</class>
</hibernate-mapping>
OrderDetails:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NorthwindNH.Model"
namespace="NW.EntityClasses">
<class name="OrderDetail" table="[dbo].[Order Details]"
optimistic-lock="none" >
<composite-id>
<key-many-to-one name="Order" column="OrderID"
access="field.camelcase-underscore" />
<key-many-to-one name="Product" column="ProductID"
access="field.camelcase-underscore" />
</composite-id>
<property name="Discount" column="Discount"
access="field.camelcase-underscore"/>
<property name="Quantity" column="Quantity"
access="field.camelcase-underscore"/>
<property name="UnitPrice" column="UnitPrice"
access="field.camelcase-underscore"/>
</class>
</hibernate-mapping>
TIA!
FB
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, .NET's most advanced O/R mapping designer.
LLBLGen Pro website: http://www.llblgen.com
Blog: http://weblogs.asp.net/fbouma
Twitter: http://twitter.com/FransBouma
Microsoft MVP (C#).
------------------------------------------------------------------------
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.