Many-to-many NHibernate mapping to a legacy app view

Question

My many-to-many relationship does not involve the standard "joining-table" approach, in which a table stores the "FK1-to-FK2" relationships.

Instead, I'm "loosely" joining to a legacy read-only view as follows:

Appointment class (based on the Appointment table)

int AppointmentId (PK)
int OrderId
List<LegacyOrder> LegacyOrders

LegacyOrder class (based on the LEGACY_ORDERS_VIEW view in our legacy system)

int OrderId (composite PK)
int VersionNumber (composite PK)

An Appointment can have many (versions of a) LegacyOrder.
A LegacyOrder can have many Appointments, but this relationship is not important in our application.

I want to populate the LegacyOrders property with all LegacyOrders for the specified OrderId. My attempt at mapping is as follows:

<class name="Appointment" table="Appointments" lazy="true">
  <bag name="Orders" table="LEGACY_ORDERS_VIEW" inverse="true">
    <key column="OrderId" />
    <many-to-many class="LegacyOrder" column="ORDER_ID" />
  </bag>
</class>

....but I'm getting "could not execute query" exceptions due to invalid SQL.

I think the table referred to in the <bag> mapping should be the "joining table".... but I don't have one.

I'm fairly sure my mapping approach is fundamentally wrong.... what's the right way to go about it?

Edit:
Thanks Radim: perhaps a better name for LegacyOrder would be LegacyOrderVersion: each record in that view corresponds to a "version" of an order, rather than an order.

i.e. An order may be for 100 units, then when say 20 units are collected, another record is written with the same OrderId but for 80 units. (I did warn you it was legacy :)

If an Appointment (in the new system) can retrieve all related LegacyOrderVersions, then it can derive useful properties such as CurrentLegacyOrderVersion and OriginalLegacyOrderVersion.

FWIW: this works great for me:

<class name="Appointment" table="Appointments" lazy="true">
  <bag name="Orders" inverse="true">
    <key property-ref="OrderId" column="ORDER_ID" />
    <one-to-many class="LegacyOrder" />
  </bag>
</class>

Answer 1

One way how to solve this a bit challenging DB structure, could be with the property-ref feature. See more details here: 5.1.10. many-to-one, working even for our many-to-many scenario.

So firstly we have to map the property, which we will use as a reference:

<class name="Appointment" table="Appointments" lazy="true">
  ...
  // the column name is coming from the Appointment table
  <property name="OrderId" column="ORDER_ID" />

So, now we have mapped the OrderId - the property (column) - which we will use to map the <bag>.

Well, honestly, now I am not sure what your thoughts were. In case that LegacyOrder would have one column mapped as key (the Order_ID) we can do it like this.

<bag name="Orders" table="LEGACY_ORDERS_VIEW" inverse="true">
    <key column="ORDER_ID" property-ref="OrderId" />
    <many-to-many class="LegacyOrder" formula="ORDER_ID" />
</bag>

But that's not reasonable, because the Order_Id is not unique. In fact the LegacyOrder view, does not seem to be the entity at all. It could be some real intermediate structure.

I would say, that what the pairing view Legacy_orders_view represents, is the map (dictionary) saying: The Order with ID == X, had these Versions.

This information, the int Version numbers, is the only thing/information I can find out as really interesting. The OrderId is representing still the same Order

Anyhow, with the proeprty-ref and more detailed knowledge what you need to achieve we can at the end have:

// I. Map
public virtual IDictionary<int, Order> OrderMap { get; set; } 

above the Version will play the role of the Key, the Order is questinable, because it will be the same Order as the OrderId says

// II. Version collection
public virtual IList<int> OrderVersions { get; set; } 

in this case we will get set of int numbers related to the OrderId. Seems to be the only interesting message we can get.

III. There must be more information, about your entity/DB model. Why does the Legacy_orders_view exists at all? What would we like to get from that "relation" at the end?