Optimistic concurrency with Entity Framework and MySQL

I'm c开发者_StackOverflowurrently developing an app using Entity Framework 4.1 and MySQL. I want to use optimistic concurrency and therefore need to create a table structure which allows EF to detect concurrency issues. My goal is something similar to this: http://blogs.msdn.com/b/alexj/archive/2009/05/20/tip-19-how-to-use-optimistic-concurrency-in-the-entity-framework.aspx.

My problem is that the timestamp-type is different in MySQL compared to MS SQL Server. In addition to that neither timestamp nor datetime offer sub-second precision in MySQL (http://feedblog.org/2007/05/26/why-doesnt-mysql-support-millisecond-datetime-resolution/). These types would therefore be quite bad in detecting concurrency-problems.

What other data-type could I use to solve this? I was thinking of maybe using a Guid. There are two potential problems with this approach though: 1. MySQL stores Guids as char(36) making them very inefficient. 2. I'm not sure if the EF requires the row-version to be strictly increasing or if it's enough that it's unique.

Big caveat: NOT TESTED - just thinking aloud.

EF supports override of SaveChanges, so perhaps one option is to define an interface such as:

interface IVersionedRow {
    int RowVersion {get;set;}
}

and add an int RowVersion property/field to both your model class(es) and the database table(s), and use partial class to implement this interface (using implicit interface implementation):

partial class Customer : IVersionedRow {}
partial class Order : IVersionedRow {}
...

Then override SaveChanges, something like:

public override int SaveChanges(SaveOptions options)
{    
    foreach (ObjectStateEntry entry in
        ObjectStateManager.GetObjectStateEntries(EntityState.Modified))
    {
        var v = entry.Entity as IVersionedRow;
        if(v != null) v.RowVersion++;
    }
    return base.SaveChanges(options);
}

that should then function (in theory - untested) as a manually implemented row-version counter. Leave change-validation enabled for RowVersion, and that should serve.

This is tested solution (for EF6 and above).

Here what you have to do in your model:

    [Table("some_table")]
    public class SomeEntity : IVersionedRow //define an interface as described previously and replace int type to long
    {
        ...
        [Column("row_version")]
        public long RowVersion { get; set; }
    }

Then in your context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<SomeEntity>()
        .Property(p => p.RowVersion).IsConcurrencyToken();
    base.OnModelCreating(modelBuilder);
}

public override int SaveChanges()
{    
    var objectContextAdapter = this as IObjectContextAdapter;
    if (objectContextAdapter != null) {
        objectContextAdapter.ObjectContext.DetectChanges();
        foreach (ObjectStateEntry entry in objectContextAdapter.ObjectContext.ObjectStateManager.GetObjectStateEntries(EntityState.Modified)) {
            var v = entry.Entity as IVersionedRow;
            if (v != null) 
                v.RowVersion++;
        }
    }
    return base.SaveChanges();
}

Now when you do update or delete operation don't forget to include DbUpdateConcurrencyException . It works perfect for me.

I have just submitted a PR to MySQL .NET Connector v6.9.10 that provides a solution for this issue that provides optimistic locking between EF and non-EF applications. See https://stackoverflow.com/a/50147396/365261 for details.