I have a bi-directional one-to-many relationship. I have class called SandlotProfile which has a property (IList generic) with a collection of DEASchedule items. Each item has a reference back to their SandlotProfile.

For some reason if I do a clear on the collection, then add the new items i want, nhibernate issues this to sql server with the inserts first, then the deletes. This violates some unique key constraints i was hoping to have on this table because it does the inserts first which causes duplicates temporarily. If i take the constraints off it works fine.

My code looks like this:

//clear the list of items

profile.SandlotProfileDEAList.Clear();

//add the new items one by one...

foreach (ListItem item in DEAScheduleList.Items)
{
SandlotProfileDEA profileDEA = new SandlotProfileDEA();
profileDEA.DEASchedule = Convert.ToInt32(item.Value);
profileDEA.Selected = item.Selected;
//profile.SandlotProfileDEAList.Add(profileDEA);

profile.AddDEA(profileDEA);
}

in class SandlotProfile:
public virtual void AddDEA(SandlotProfileDEA profileDEA)
{
profileDEA.SandlotProfileObject = this;
this.SandlotProfileDEAList.Add(profileDEA);
}

Below are the mappings. Thanks.


<class name="SandlotProfile" table="SandlotProfile">
<!-- Primary key -->
<id column="SandlotProfileID" type="int" name="ID" access="field.camelcase-underscore">
<generator class="native">
</generator>
</id>



<!-- One-To-Many relationships -->
<bag name="SandlotProfileDEAList" inverse="true" lazy="true" cascade="all-delete-orphan" >
<key column="SandlotProfileID" />
<one-to-many class="SandlotProfileDEA" />
</bag>

<!-- Standard Properties/Fields -->
<property column="UserId" type="System.Guid" name="UserId" not-null="true" access="field.camelcase-underscore" />
<property column="FirstName" type="System.String" name="FirstName" not-null="true" access="field.camelcase-underscore" />
<property column="LastName" type="System.String" name="LastName" not-null="true" access="field.camelcase-underscore" />

</class>

<class name="SandlotProfileDEA" table="SandlotProfileDEA">
<!-- Primary key -->
<id column="SandlotProfileDEAID" type="long" name="ID" access="field.camelcase-underscore">
<generator class="native">
</generator>
</id>

<!-- Many-to-one relationships -->
<many-to-one column="SandlotProfileID" name="SandlotProfileObject" class="SandlotProfile" not-found="ignore" cascade="save-update" access="field.camelcase-underscore" />

<!-- Standard Properties/Fields -->
<property column="DEASchedule" type="System.Int32" name="DEASchedule" not-null="true" access="field.camelcase-underscore" />
<property column="Selected" type="System.Boolean" name="Selected" not-null="true" access="field.camelcase-underscore" />
</class>

Clearing the list isn't enough, you also have to set the back reference to null. Without that, the items would normally not be deleted. So I'm a bit confused why you see deletes at all. Maybe because you insert new items with the same foreign key ? Try to clear the back reference while clearing the list.

I tried that, but NH gave me this error:

You may not dereference an collection with cascade="all-delete-orphan"

Is there a different cascade option i should be using? This doesn't make sense to me.

I also tried setting cascasde to "all" but that doesn't do ANY deletes. It just keeps adding more items, despite the fact that i dereferenced the old collection.

Can you try to remove a single item and flush the list:

profile.SandlotProfileDEAList.Remove(profileDEA);
profileDEA.SandlotProfileObject = null;

and see if that works ? Try to set lazy to false. Btw. with dereferencing the old collection you mean setting the SandlotProfileObject in the item to null ?