Mapping complicated references in NHibernate

Question

I am trying to build the following association in NHibernate (the base Entity class contains an Id property, it and the attributes are from S#arp Architecture)

public class Terminal : Entity {
    public virtual string Name { get; set; }
}
public class Order : Entity {
    [NotNull]
    public virtual Terminal Terminal { get; set; }
}

Where the data on order is stored in ORDERS and data on terminals in stored in the TERMINALS tables. The problem is that there is no direct link between the two tables. If I had an id for ORDERS.ORDER_ID I would fetch the corresponding TERMINALS row with the following SQL.

select t.* 
from ORDERS o 
     inner join cust_prod cp on (o.CUST_PROD_ID = cp.CUST_PROD_ID)
     inner join customer_terminal ct on (cp.CUSTOMER_TERMINAL_ID = ct.CUSTOMER_TERMINAL_ID)
     inner join terminal t on (ct.TERMINAL_ID = t.TERMINAL_ID)
where o.ORDER_ID = :p_order_id

How do I map this in NHibernate? Preferably how do I map this in Fluent NHibernate but I think I could figure it out from the XML. Also, given that the legacy database I'm working with is full of chains like this are there any best practices suggestions?

Answer 1

I'm pretty sure your options would be to:

1) Map against raw SQL (look at the NHibernate doc definition for sql-query: nh docs).. The section on mapping updates / inserts is right after.

2) Or, introduce at least entities in the middle to handle holding the relationships in cust_prod and customer_terminal. If those tables are using composite keys for the PKs then you will want to look at how to map composite-id and key-many-to-one.

In the second option, you'd then map either a one-to-one (or bi-directional many-one) on the join entities.

3) I haven't looked at stored procs in forever, but I believe NH 2.0+ introduced better sproc support. That might be a viable third option.