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.

Reply via email to