NHibernate Bag Mapping

Question

I need a hand mapping a collection.

I am using a join table to hold references to rooms ids and calEvent Ids. However, the look ups will mainly be based on the room's buildingID - so I'd like to add buildingID to the following mapping. Does anyone know how to do so?

I've looked through the NHibernate docs but can't find anything.

<bag name="rooms" table="tb_calEvent_rooms" lazy="false">
  <key column="calEventID"/>
  <many-to-many class="BasicRoom" column="roomID"/>
</bag>

Class:

public class BasicRoom 
{
    private long _id;
    private long _buildingID;
    private string _roomName;

Any hints/help much appreciated.

Reasoning:

A room will never change building so if I add buildingID to the join table; I will reduce the complexity of the select statement from something like -

SELECT *
FROM
  dbo.tb_calEvent_rooms
  INNER JOIN dbo.tb_calEvents ON (dbo.tb_calEvent_rooms.calEventID = dbo.tb_calEvents.id)
  INNER JOIN dbo.tb_rooms ON (dbo.tb_calEvent_rooms.roomID = dbo.tb_rooms.id)
  INNER JOIN dbo.tb_buildings ON (dbo.tb_rooms.buildingID = dbo.tb_buildings.id)
WHERE
  dbo.tb_buildings.id = 54

To

SELECT *
FROM
  dbo.tb_calEvents
  INNER JOIN dbo.tb_calEvent_rooms ON (dbo.tb_calEvents.id = dbo.tb_calEvent_rooms.calEventID),
  dbo.tb_buildings
WHERE
  dbo.tb_buildings.id = 54

Is my reasoning correct?

Asbjørn Ulsberg Asbjørn Ulsberg · Accepted Answer · 2009-02-03T07:50:34

Remove the column attribute from the many-to-many element and add it as a child node insead, alongside an additional column element referencing the buildingID:

<bag name="rooms" table="tb_calEvent_rooms" lazy="false">
  <key column="calEventID"/>
  <many-to-many class="BasicRoom">
    <column name="roomID"/>
    <column name="buildingID"/>
  </many-to-many>
</bag>

That should enable you to query based on the buildingID as well as roomID.

NHibernate Bag Mapping

1 Answers