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.
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(); }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With