I did not realize the following is true in LINQ to SQL: Calling SubmitChanges fires ChangeSet changes in the following order:
- Inserts
- Updates
- Deletes
Normally, this might not be a big deal, and you might never notice it, but it was the source of some pain on my current project.
Consider an order-entry screen that allows the user to add/edit/delete line items on an order. The order-detail records are keyed by order-number/line-number, and this is the source of the problem. If the user deletes line #1 in the order, what was originally line #2 gets re-ordered as line #1. When the user decides they are done editing the order, they will then hit the save button and the queued up add/edit/delete operations are committed via the LINQ to SQL SubmitChanges command.
Fortunately, we were able to change the primary-key on our order-detail table, but we effectively lost some automatic data-integrity since we were previously able to ensure uniqueness on order-number/line-number. For teams that don’t have the luxury of changing their database keys, this design decision by the LINQ to SQL team may be cause to move away from LINQ to SQL.
Why the LINQ to SQL team decided to not submit changes in the order of operations, or allow the user to specify a non-default order to submit changes is beyond me. I’m sure they had valid reasons for this decision, but it’s a bit short-sighted in that this interaction pattern is fairly common in order-entry system.
This could perhaps been avoided by stamping various LINQ class members as virtual and letting us developers (consumers) override behaviors as we need – and assume the risks that come with overriding the framework code.
