DbUpdateConcurrencyException using Entity Framework 6 with MySql

concurrency entity-framework entity-framework-6 mysql

Question

I'm having trouble with concurrency checks using EF6 and MySQL.

The problem I'm having is that I get a concurrency exception thrown when I try to save data to the database. If you examine the sql that is output to the console it tries to query the concurrency field from the database using the old value in the where clause. Because this field has been updated by the database.

Environment:

  • Windows 7 64 bit
  • Visual Studio 2013

Nuget packages installed:

  • EF 6.0.1
  • MySql.ConnectorNET.Data 6.8.3.2
  • MySql.ConnectorNET.Entity 6.8.3.2

Demo Database SQL:

DROP DATABASE IF EXISTS `bugreport`;
CREATE DATABASE IF NOT EXISTS `bugreport`;
USE `bugreport`;

DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
  `TestId` int(10) NOT NULL AUTO_INCREMENT,
  `AStringField` varchar(50) DEFAULT NULL,
  `DateModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`TestId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

INSERT INTO `test` (`TestId`, `AStringField`, `DateModified`) VALUES
    (1, 'Initial Value', '2014-07-11 09:15:52');

Demo code:

using System;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

namespace BugReport
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new BugReportModel())
            {
                context.Database.Log = (s => Console.WriteLine(s));

                var firstTest = context.tests.First();
                firstTest.AStringField = "First Value";

                // Exception is thrown when changes are saved.
                context.SaveChanges();              

                Console.ReadLine();
            } 
        }
    }

    public class BugReportModel : DbContext
    {
        public BugReportModel()
            : base("name=Model1")
        {

        }

        public virtual DbSet<test> tests { get; set; }
    }


    [Table("test")]
    public class test
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int TestId { get; set; }

        [StringLength(50)]
        public string AStringField { get; set; }

        [ConcurrencyCheck()]
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        [Column(TypeName = "timestamp")]
        public System.DateTime DateModified { get; set; }
    }
}

Update: Filed bug with MySql.

1
2
7/16/2014 10:26:26 AM

Popular Answer

You should be trying to use the DB Timestamp / Rowversion feature. In EF you declare a ByteArray and nominate it as the Concurrency check field. DB sets the value on creation. All subsequent updates can check the value hasnt changed DB updates rowversion as appropriate. This approach works on SQL server. It should behave the same way on MYSql.

    public  abstract class BaseObject  {
    [Key]
    [Required]
    public virtual int Id { set; get; }

    [ConcurrencyCheck()]
    public virtual byte[] RowVersion { get; set; }

    }

or via fluent if you like // Primary Key this.HasKey(t => t.Id);

        // Properties
        //Id is an int allocated by DB , with string keys, no db generation now
        this.Property(t => t.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); // default to db generated

        this.Property(t => t.RowVersion)
            .IsRequired()
            .IsFixedLength()
            .HasMaxLength(8)
            .IsRowVersion(); //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Docu on the optimistic concurrency pattern

1
7/11/2014 7:37:30 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow