Managing child lists with LINQ to SQL

Question

I have a simple parent-child situation where the parent can have multiple children. The user can update the list of children at will by selecting or deselecting them from a listbox. I try updating the child list using code like below but I get a SqlException:

Violation of PRIMARY KEY constraint 'PK_Child_1'. Cannot insert duplicate key in object 'dbo.Child'.

It appears that LINQ is inserting the new children before deleting the existing ones. I'm sure there is a simple pattern for handling this but I'm stumped.

context.DeleteAllOnSubmit(parent.Children);
foreach (string childname in listBox1.SelectedItems) {
    parent.Children.Add(new Child(parentkey, childname));
}
context.SubmitChanges();

Each parent has a unique key (a GUID) and all parent and child columns are non-nullable. The child table is a simple two-column table with the parent key and a varchar value, with a compound primary key consisting of both columns.

Thanks

Answer 1

The problem is that you're adding the new items before the old ones have been deleted.

Linq-to-SQL has a clearly defined order of operations on a SubmitChanges() call (you should definitely also use SubmitChanges() - not Save() !):

* Inserts
* Updates
* Deletes

(for reference, see here and here)

The trouble here is - you seem to add back child nodes that have the same keys as the ones you delete away in the DeleteAllOnSubmit() call.

But because the INSERT of the new items comes before the deletion, you end up with a conflict.

What you need to do is one of two things:

• either you delete the child nodes and then call SubmitChanges() before adding the new items again

OR:

• you change your logic so that child nodes that still exist aren't first deleted and then re-added; change your logic to only delete those items that are really deleted (and not added back), and insert only those that are truly new

With either of these two steps, you should get your system working.

Marc