NHibernate Bidirectional many-to-many Mapping for 2 Lists

I'm trying to generate the correct mapping for 2 classes that have a collection of each other within them.

I currently have a Zone and a Vehicle class. The Zone class contains a list of Vehicles that contain the Zone. The Vehicle class contains a list of Zones that contain the Vehicle. As you can see the two lists are directly related to each other. However, my mapping keeps giving me a foreign key contraint error when trying to save one of my objects.

Can someone explain what I am doing wrong?

Here is the mapping for the Vehicle class:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class xmlns="urn:nhibernate-mapping-2.2" name="EMTRAC.Devices.Device, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="`Device`">
    <id name="PK" type="System.Int64, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="PK" />
      <generator class="identity" />
    </id>
    <version name="LastModifiedOn" column="LastModifiedOn" type="timestamp" access="field.pascalcase-underscore" />
    <joined开发者_开发技巧-subclass name="EMTRAC.Vehicles.Vehicle, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
      <key>
        <column name="Device_id" />
      </key>      
      <component name="Zones" access="property">
        <bag name="_list" cascade="save-update" access="field" table="VehicleZones">
          <key>
            <column name="Veh_id"/>
          </key>          
          <many-to-many class="EMTRAC.Zones.Zone, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"/>
        </bag>
      </component>
      <property name="ID" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
        <column name="ID" />
      </property>      
    </joined-subclass>
  </class>
</hibernate-mapping>

Here is my mapping of the Zone class:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class xmlns="urn:nhibernate-mapping-2.2" name="EMTRAC.Zones.Zone, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="`Zone`">
    <id name="ID" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="PK"/>
      <generator class="identity" />
    </id>
    <version name="LastModifiedOn" column="LastModifiedOn" type="timestamp" access="field.pascalcase-underscore" />
    <property name="ID" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="ID" />
    </property>    
    <component name="Vehicles" access="property">
      <bag name="_list" cascade="save-update" access="field" table="VehicleZones" inverse="true">
        <key>
          <column name="Zone_id" not-null="false"/>
        </key>
        <many-to-many class="EMTRAC.Vehicles.Vehicle, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"/>
      </bag>
    </component>
  </class>
</hibernate-mapping>

I am saving the Zones and Vehicles via:

    using (var session = _sessionFactory.OpenSession())
{
    foreach (Zone zone in Program.data.Zones.list)
    {
        session.SaveOrUpdate(zone);
    }
    foreach (Vehicle veh in Program.data.Vehicles.list)
    {
        session.SaveOrUpdate(veh);
    }
}

Afterwards I add the Zones to the Vehicle list and the Vehicles to the Zone list and then I am attempting to save the List via:

using (var session = _sessionFactory.OpenSession())
{              
    foreach (Zone zone in Program.data.Zones.list)
    {
        foreach (Vehicle veh in Program.data.Vehicles.list)
        {
            veh.Zones.Add(zone);
            zone.Vehicles.Add(veh);
        }
    }

    using (var tx = session.BeginTransaction())
    {
        foreach (Vehicle veh in Program.data.Vehicles.list)
        {
            session.Update(veh.Zones);
        }
        tx.Commit();
    }

}

At which time the Commit call throws the foreign key constraint exception. What am I doing wrong?

Okay, I finally figured out what I was doing wrong. My finalized version of the mappings were:

Vehicle:

    <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class xmlns="urn:nhibernate-mapping-2.2" name="EMTRAC.Devices.Device, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="`Device`">
    <id name="PK" type="System.Int64, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="PK" />
      <generator class="identity" />
    </id>
    <version name="LastModifiedOn" column="LastModifiedOn" type="timestamp" access="field.pascalcase-underscore" />    
    <joined-subclass name="EMTRAC.Vehicles.Vehicle, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
      <key>
        <column name="Device_id" />
      </key>      
      <component name="Zones" access="property">
        <bag name="_list" cascade="save-update" access="field" table="VehicleZones" inverse="true">
          <key>
            <column name="veh_id" not-null="true"/>
          </key>
          <many-to-many class="EMTRAC.Zones.Zone, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"/>
        </bag>
      </component>
      <property name="ID" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
        <column name="ID" />
      </property>      
    </joined-subclass>
  </class>
</hibernate-mapping>

Zone:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class xmlns="urn:nhibernate-mapping-2.2" name="EMTRAC.Zones.Zone, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="`Zone`">
    <id name="ID" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="PK"/>
      <generator class="identity" />
    </id>
    <version name="LastModifiedOn" column="LastModifiedOn" type="timestamp" access="field.pascalcase-underscore" />
    <property name="ID" type="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      <column name="ID" />
    </property>
    <component name="Vehicles" access="property">
      <bag name="_list" cascade="save-update" access="field" table="VehicleZones">
        <key>
          <column name="veh_id" not-null="true"/>
        </key>
        <many-to-many class="EMTRAC.Vehicles.Vehicle, EMTRAC_v3, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"/>
      </bag>
    </component>
  </class>
</hibernate-mapping>

They main thing was causing me all of my issues was the inverse="true" being set on the Zone mapping side.

If you note I removed this flag from the Zone side and placed it on the Vehicle end. This flag informs Hibernate which class is responsible for maintaining the relationship. I was saving the Vehicle and not the Zone which was causing the errors dealing with the foreign keys.

Since I actually wanted the Vehicle to be responsible for saving this relationship, I needed to have the inverse set to true on the Vehicle side. I also needed to flag the cascades as "save-update" in order for the Vehicle to cascade the updates to the Zones and link the relationship to the Zone. Thus saving a single vehicle links the zones in the table as well as saves the Zone object as well, effectively fixing the errors I was encountering during the saving process before.

Now the saving works fine. Although I would recommend an approach towards saving these items along the lines of:

foreach (Vehicle veh in Program.data.Vehicles.list)
{
    using (ITransaction tx = session.BeginTransaction())
    {
        session.Save(veh);

        //  Commit transactions
        tx.Commit();
    }
}

If you have a large number of classes in the two lists.

The reason for this is that your transaction is going to be locking the items in the database during until you call the commit. Once the commit is called, all the updates and everything that was queued are actually carried out. So the save is merely sending the actual transactions to the database to be issued once the commit is called. The commit itself is where all the actual work is taken place and once it is called the actual items are stored in the database. Obviously you don't want a transaction taking an extremely long period of time.

In my case I was trying to save 5000 vehicles and 9600 zones. This would end up with the table responsible for storing the two lists containing 48 million rows, hence the reason for creating a separate transaction and commit call per vehicle. Using this approach prevents these items from being locked during the entire duration of this 48 million inserts and is much more efficient. Instead the commit is called after saving each vehicle, so aside from the very first Vehicle being saved (since the first save has to save all the zones as well if they don't exist), there are only going to be 9600 commands issued per transaction. This is obviously much better than 48 million.

However I'd like to note that this was not a major issue with a much smaller sample size. It's just something for everyone to take into account.

Hope this helps anyone who stumbles across this.