So I have many rooms
that can contain any number of devices
. Those devices
can be in many rooms
. This is straightforward sofar. Now I need to be able to add multiple instances of the same device
to a single room
. That is, a given room might have multiple instances of the same projector model.
This is proving surprisingly difficult to do. My first thought was to add a simple count
column to the join table, but according to this answer, doing so isn't straightforward. Maybe I'll return to this solution, but for now I'd like to look at a plan B.
Plan B would just involve adding multiple rows containing the same device in the same room. Something like this...
for(var i = 0; i < newDevice.count; i++)
{
room.Devices.Add(device);
}
...which doesn't work. `.Add(device) will only add the one row, no matter how many times I loop through. This question might be relevant.
How would I go about adding multiple instances of the same row to the Rooms_Devices
table in this scenario?
EDIT FOR CLARITY:
What I am looking for is a way to create multiple rows of the the same data to my many-to-many table. That is, I want my above snippet to produce rows similar to this:
Rooms_Devices
---------------------------
id | room_id | device_id
1 | 22 | 38 <--- same room, same device, three times
2 | 22 | 38
3 | 22 | 38
The code above instead produces this:
Rooms_Devices
---------------------------
id | room_id | device_id
1 | 22 | 38 <----just one row, not three
The problem being .Add()
doesn't want to create the same row multiple times (apparently). I am using Entity Framework.
EDIT FOR ANSWER:
The answer below is correct, but it didn't make immediate sense to me, so I'll summarize: In a many-to-many join table, the id pair (room_id
, device_id
) must be unique, so what I was trying to do can't be done. If you want to add a column to a many-to-many relationship, you have to build a model for the table. That is, I now have a Devices_Rooms
model. This necessitates changing a bunch of other code (and making that code more difficult to read), but it appears to be the only way to go.