CODEWITHSUNDEEP
sql-servernhibernatemapping
user17510
user17510

Reputation: 1549

NHibernate Bag Mapping

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?

Upvotes: 1

Views: 7548

Answers (1)

Asbj&#248;rn Ulsberg
Asbj&#248;rn Ulsberg

Reputation: 8820

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.

Upvotes: 1

Related Questions