使用Entity Framework 6和MySql的DbUpdateConcurrencyException [英] DbUpdateConcurrencyException using Entity Framework 6 with MySql

问题描述

我在使用EF6和MySQL进行并发检查时遇到了麻烦.

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

我遇到的问题是，当我尝试将数据保存到数据库时出现了并发异常.如果检查输出到控制台的sql，它将尝试使用where子句中的旧值从数据库查询并发字段.因为此字段已由数据库更新.

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.

环境:

• Windows 7 64位
• Visual Studio 2013

已安装Nuget软件包:

Nuget packages installed:

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

演示数据库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');

演示代码:

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; }
    }
}

更新: 向MySql提交 bug .

Update: Filed bug with MySql.

推荐答案

您应该尝试使用DB Timestamp/Rowversion功能. 在EF中，您声明一个ByteArray并将其指定为并发检查字段. DB在创建时设置值.所有后续更新都可以检查尚未更改的值 数据库将适当地更新行版本.这种方法适用于SQL Server. 它在MYSql上的行为应相同.

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; }

    }

或通过流利的方式(如果您愿意) //首要的关键 this.HasKey(t => t.Id);

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

关于乐观并发模式的文档

这篇关于使用Entity Framework 6和MySql的DbUpdateConcurrencyException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！