SQL rowversion support by entity framework

user3058082 picture user3058082 · Mar 5, 2014 · Viewed 11k times · Source

My project uses EF (tested with version 4 using self-tracking template and with version 5 using default templates, all database-first) against SQL Server 2012. The database tables have each a rowversion (timestamp) column defined.

Using EF in it core, meaning my code on database updates looking so:

using (var db = new MyContext())
{
  //db.Entry(myInstance).State = EntityState.Modified;
  db.SaveChanges();
}

does not trigger any rowversion alerts. I run parallel clients, each reads the same record, makes a change to it and then each writes it to the database. All updates are accepted, no concurrency is applied.

Do I have to work with stored procedures for my update commands (with a where clause that states my rowversion value) to have EF acknowledge the "built-in" concurrency or is there another way (configuration, specific method calls) to make my code work?

Answer

user3058082 picture user3058082 · Mar 19, 2014

Here's the answer (I've given it a couple of weeks to POC):

  1. RowVersion (or the TimeStamp field type) in SQL is a field as any other (except for being mandatory and self incrementing). There is a specific database handling of its value on updates (i.e. incrementing it), but there is no specific database handling comparing its value before update.
  2. EF on the other side allows you to define ConcurrencyMode for each field (edmx). You might, if you want, mark all your fields with ConcurrencyMode=Fix (instead of the default None), and thus include all of them within the update's where-clause (comparing the entity's original values with the record's current values in the database). But it's easier to set one field per entity, i.e. the RowVersion field, with that mode. Especially since the only party maintaining it for you is the database.
  3. Once you've done that, you're sure to get the System.Data.OptimisticConcurrencyException isolation error. You still have to keep away from EF workflows which manipulate your object sets, such as the use of myObjectSet.Attach(myEntity), which makes a trip to the database to fetch the current data and merges your changes into it. Since the RowVersion field is normally unchanged, the update will trigger with the current value from the database, and will not result in the concurrency exception.