NHibernate Bag Mapping

Question

Hi 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?

Answer 1

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.