Full Concurrency Check on Updated Columns

[tbasallo]

Is there a feature or easy way (using Snapshot?) that would allow for a full versioning check? I didn't find anything, but I want to make sure I didn't anything.

Idea being that if I am updating 4 columns, those columns would have to have the original value to perform the update. Otherwise, no update is performed. This would indicate that one of the fields was changed outside the current scope.

[schotime]

Only by using the version column support.

https://github.com/schotime/NPoco/wiki/Version-column-support

[tbasallo]

@schotime how do you feel about something like the below as an additional overload on update for a concurrency option.

The overload would take a snapshot object (instead of the column names) so that we can get the column names and the original values. And then add the changed columns and original values to the where clause. If no update is performed throw a concurrency exception - or even make that an option with an additional parameter (bool throwConcurrencyException: true/false) <- since we return 1/0 based on the update, I don't know if an exception is necessary for every use case.

Currently from wiki db.Update(user, snapshot.UpdatedColumns()); // Only the Name column will be updated

Add overload of: db.Update(user, snapshot); // Only the Name column will be updated and it must match the current value

[tbasallo]

@schotime any interest in doing something like this? I'm about to need a solution for this in a project I'm on. But I don't want to have my own fork. But I'll do the work if it's something you'd integrate.

[schotime]

You're not interested in using a row version column?

[schotime]

It can definitely be integrated however we already use an extension method that takes snapshot directly and calls updated Columns() internally.

Also which colums do you use in the where. That would have to be carefully considered.

[tbasallo]

The issue with with row version is that it changes if any column changes. In a table that is 50 columns wide and I'm updating the address, I don't care that the user's cat's name changed.

But if I'm updating the zip code and the address (and zip code) changed since I made my change, I may not want to overwrite that.

I think it would ONLY work with the snapshot/columns overload so that we know which columns to check. IN this case we add the columns we are updating to the WHERE clause (if not already there). If the DB returns 0 then we know there was no update which is because one of the reasons: the record doesn't exist OR one of the columns changed.

At this point we can either return the 0 and the dev can handle in app code - no change there or throw a DbConcurrencyException (I think this is what EF does). I would make it an option.