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?
Best Answer
Remove the
column
attribute from themany-to-many
element and add it as a child node insead, alongside an additionalcolumn
element referencing thebuildingID
:That should enable you to query based on the
buildingID
as well asroomID
.