DbUpdateConcurrencyException using Entity Framework 6 with MySql

concurrency entity-framework entity-framework-6 mysql

Question

Using EF6 and MySQL, I'm having issues with concurrency checks.

When I try to save data to the database, a concurrency exception is issued, which is my problem. If you look closely at the SQL that is printed to the console, you'll notice that it attempts to query the database's concurrency field using the previous value in the where clause. because the database has changed this field.

Environment:

  • 64-bit Windows 7
  • V. Studio. 2013

Nuget packages were set up:

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

SQL Demo Database:

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 software

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: bug been entered into MySql.

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

Popular Answer

Use the DB Timestamp / Rowversion functionality as much as possible. You declare a ByteArray in EF and choose it as the field for the concurrency check. The value is set by DB at creation. All subsequent modifications may verify that the value hasn't changed, and the database updates the rowversion as needed. On SQL server, this strategy is effective. On MYSQL, it ought to behave the same way.

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

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

    }

or, if you want, through Fluent / Primary Key this. Id = HasKey(t);

        // 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(); //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Documentation on the constructive 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