Linq to SQL: execution order when calling SubmitChanges()

Question

I have 2 related database tables which in simplified form look like this

Product(   product_id,   name )  ProductSpecs(   spec_id,   product_id,   name,   value ) 

Foreign key is set via product_id field and ProductSpecs table has a unique constraint on (product_id, name) pair.

Now in my ASP.NET MVC application when user edits product specs and saves the data I delete old specs and insert all as new ones.

I do this by first calling DataContext.DeleteAllOnSubmit() and providing current (old) ProductSpecs as a parameter, and then I add new specs to the Product.ProductSpecs collection.

Then I call DataContext.SubmitChanges() and get an error that my unique constraint was violated.

By looking at the SQL statements returned by DataContenxt.GetChangeText() I can see that INSERTs are executed before DELETEs (even though I called DeleteAllOnSubmit() before Add).

What is the reason of this behavior and how to fix or workaround it?

Thanks.

Answer 1

Yes, for some reason, Linq to SQL perform all deletes as the last thing. And there is no way to change that.

Or maybe there is. I haven't looked into the codegen DataContext to see if we can override something there.

You can call SubmitChanges() as many times you want. My workaround when I need to delete as the first thing, is mark my deletions, call SubmitChanges(), then perform inserts and updates, and call SubmitChanges once again.

You can wrap everything inside of a TransactionScope:

    var deletables =         from toDelete in db.NamedValues         where toDelete.Name == knownValue.Name         select toDelete;      using (var scope = new TransactionScope())     {         db.NamedValues.DeleteAllOnSubmit(deletables);         db.SubmitChanges();          db.NamedValues.InsertOnSubmit(knownValue);         db.SubmitChanges();          scope.Complete();     }