LinqToSQL: Change conflict exceptions arising from auto-updated fields

Question

I've run into a Change Conflict Exception that I can't quite determine the reason for.

From the ChangeConflictException documentation:

Thrown when an update fails because database values have been updated since the client last read them.

In the case I'm looking at though, the database values haven't been modified. The only modifications taking place before the exception is being thrown are on the object in memory.

I initially had a database trigger which updated a timestamp on the record whenever it was modified. The trigger itself was causing a ChangeConflictException, so I removed it and instead extended the generated linq-to-sql dbml object in the following way:

 public partial class TerribleName {

    partial void OnCreated() {
        PropertyChanged += RecordUpdated;
    }

    void RecordUpdated(object sender, PropertyChangedEventArgs e) {
        //see if the field being updated was "LastUpdate"
        if (e.PropertyName != "LastUpdate") {
            this.LastUpdate = DateTime.Now; //this line triggers PropertyChanged again
        }
    }
}

I've employed this code based "trigger" a number of times, but in this case something is off (likely my own understanding).

I have a code block like this:

TerribleName name = new TerribleName();
name.First = "X Æ A-12"; //fires the trigger, updating the LastUpdate column (in memory)
name.Last = "Musk"; //fires the trigger, updating the LastUpdate column (in memory)

dbContext.TerribleNames.InsertOnSubmit(name);
dbContext.SubmitChanges(); //commits the record to the database
...
//a bunch of reads take place to validate the input - no modifications to the object happen
...
name.Validated = true; //fires the trigger, updating the LastUpdate column (in memory)
dbContext.SubmitChanges();//This now throws a ChangeConflictException

I just can't wrap my head around why this generates a Change Conflict Exception. Examining the exception, I can see that there are multiple Member conflicts, the first of which doesn't appear to be a conflict at all (Edit: it is in conflict, I didn't see the precision initially, have added it below):

First:

-occ.MemberConflicts[0] {System.Data.Linq.MemberChangeConflict} System.Data.Linq.MemberChangeConflict
    CurrentValue    {9/25/2020 10:44:26.6908779 AM} object {System.DateTime}
    DatabaseValue   {9/25/2020 10:44:26.6900000 AM} object {System.DateTime}
    IsModified  false   bool
    IsResolved  false   bool
    Member  {System.DateTime RecordDate}    System.Reflection.MemberInfo {System.Reflection.RuntimePropertyInfo}
    OriginalValue   {9/25/2020 10:44:26.6908779 AM} object {System.DateTime}

Second:

-occ.MemberConflicts[1] {System.Data.Linq.MemberChangeConflict} System.Data.Linq.MemberChangeConflict
    CurrentValue    {9/25/2020 10:44:29.7227277 AM} object {System.DateTime}
    DatabaseValue   {9/25/2020 10:44:26.6930000 AM} object {System.DateTime}
    IsModified  true    bool
    IsResolved  false   bool
    Member  {System.Nullable`1[System.DateTime] LastUpdate} System.Reflection.MemberInfo {System.Reflection.RuntimePropertyInfo}
    OriginalValue   {9/25/2020 10:44:29.7072610 AM} object {System.DateTime}

It seems to me that I'm just assigning a new value to the LastUpdate column in memory, more or less the same way I assigned the Validated flag. Why is this causing a ChangeConflictException?

My best guess is that there is something different about the context in which the PropertyChanged event is running which changes how the assignment affects the consistency of the object. If this is the case, how can I avoid the problem?

Can you use a database trigger to manage `LastUpdated` instead? https://stackoverflow.com/questions/7737945/how-to-create-trigger-for-auto-update-modified-date-with-sql-server-2008 — Robert Harvey, Sep 25 '20 at 15:10
@RobertHarvey No, the database trigger also causes the change conflict exception, especially because with a trigger the update is happening in a different context altogether. — hobwell, Sep 25 '20 at 15:14
The exception is justified. The object *has* changed, due to the first `SumbitChanges`. *Don't* call `SubmitChanges` twice, call it just once *after* you've made all changes. — Panagiotis Kanavos, Sep 25 '20 at 15:17
The DbContext is neither a database model nor a connection. It's a Unit-of-Work, meant to be used only as long as needed and disposed. During its lifetime it tracks all changes *without* storing them to the database, without even an active connection. That's Good - disconnected operations are exponentially more scaleable than having a global connection and accumulating locks. If you dispose/discard the DbContext without calling SubmitChanges, nothing is saved, thus getting UoW/transaction semantics by default — Panagiotis Kanavos, Sep 25 '20 at 15:20
Why are you calling SubmitChanges twice? Why does `//a bunch of reads take place to validate the input ` happen *after* writing to the database? Validation *after* writing isn't helpful - any bad data are already in the database — Panagiotis Kanavos, Sep 25 '20 at 15:22
@PanagiotisKanavos, that is a good point (someone else's code). I think in this case the insert could indeed wait. Might there be other potential cases where dependency on related entities might necessitate updating the object twice though? Would re-selecting the object work in such cases? BTW, that's a very helpful description of a DbContext, thanks! I had assumed that calling SubmitChanges would synchronize the object with the db, resetting the consistency checks (such that a second update wouldn't fail). — hobwell, Sep 25 '20 at 15:43
@PanagiotisKanavos I double checked, the validation that is taking place is for another object that needs to be inserted after this one, so the validation is reading from this object to get information about the consistency of another object. Still, this insert could be delayed until after that validation. — hobwell, Sep 25 '20 at 15:46
Not my description - Check Gunnar Peipman's [No need for repositories and unit of work with Entity Framework Core](https://gunnarpeipman.com/ef-core-repository-unit-of-work/). — Panagiotis Kanavos, Sep 25 '20 at 15:52
@PanagiotisKanavos if it's not too much trouble, would you mind condensing your comments into answer so I can mark this as answered? — hobwell, Nov 23 '20 at 19:02

LinqToSQL: Change conflict exceptions arising from auto-updated fields

0 Answers0