How to compare entity new values with current database values?

[Zefek]

Hello,

I would like to ask about data comparison between entity and database state. Reason why I would ask about it is log some changes. For example: I have two users which load customer entity in same time from database. It means both users have customer with name Test. User A changes customer name to TestA and user B changes customer name to TestB. User A saves entity into database first so there is some audit record "User A changed customer name from Test to TestA".
When User B wants to save I compare old and new values from Tracker and I get audit record "User B changed customer name from Test to TestB". It is allright.
But is there possibility to get record for audit "User B changed customer name from TestA to TestB" by EF because TestA value is in database?
I saw some solution when entity has ConcurrencyToken property which was changed before save so SaveChanges end with ConcurrencyException. In catch block there was code that entity properties (state) were saved, entity was reloaded and then entity was recovered by saved properties but concurrency token property and then SaveChanges was called again.

I think solution with concurrency token could work but it is not good because nobody knows how it works after two years of development and it is very nonintuitive. Now there is requirement user wants to know there is another user which changed data before but concurrency token is used for another reason.
I know about system-versioned (temporal) tables on MSSQL (temporal tables are not approved by customer because of fear of database size and performance). But is there another possibility how to compare entity with database state before save changes to database through EF? Or should audit log be solved on database level?

Thank you.

[ajcvickers]

@Zefek

I saw some solution when entity has ConcurrencyToken property which was changed before save so SaveChanges end with ConcurrencyException.

Optimistic concurrency like this is the typical pattern for dealing with this situation, regardless of whether you are using EF or not.

I think solution with concurrency token could work but it is not good because nobody knows how it works after two years of development and it is very nonintuitive.

Documentation is here: Handling Concurrency Conflicts. Can you provide specifics on the issues you are having or why you feel it is unintuitive?

[Zefek]

Problem what I see is in solution which I describe. What I need is how to compare values in database with entity. How this solution do it is about change concurrency token property before SaveChanges. It throws ConcurrencyException because concurrency token property has different value from database. Then I save entity and reload it from database and restore state which I save before reloading but concurrency token property. It means I have entity with database state and I can create compare changes to database state and not when entity was loaded first time.
It is because when I compare entity old values and new values I will write into log UserA changed customer name from Test to TestA. UserB changed customer name from Test to TestB. But UserA save before UserB so UserB changes TestA to TestB in database. This solution is about reload entity before save to compare database value with changes made by user.

I don't think Optimistic Locking or Concurrency Conflicts in EF is not intuitive. I describe solution which use optimistic locking to compare changes with database value - it is, I think, nonintuitive use of concurrency token.
What I need to know is that if there is anybody who solved similar problem how to compare entity with record in database for log changes in database.

Is it more clear what I mean?

[ajcvickers]

@Zefek The only way you can reliably do this is to start a transaction before reading the value for Customer.Name, and then update it and call SaveChanges inside that same transaction. (Theoretically, this could also be done with "select for update", which is the same concept but lighter-weight than a full transaction: see #26042.)

Anything else involves a race condition between the read and the update. This is why "optimistic concurrency" was invented. Instead of pessimistically doing everything in a transaction since something else might change it while we're working with it, we instead optimistically assume that we're the only one making a change, and then deal with the (hopefully) rare case where two changes are made concurrently.

[Zefek]

@ajcvickers Thank you very much. I didn't know about SELECT FOR UPDATE. I know how optimistic concurrency works. But in solution what I mentioned concurrency token is changed each time before SaveChanges so concurrency exception is thrown even if no one changes record.
There could be deadlocking when transaction or pessimistic concurrency is used so optimistic concurrency should be advantage. But it depends on case.