How to solve DbUpdateConcurrencyException for EntityFramework with MySql

Question

I keep getting this error when I attempt to save my database changes:

> {"Store update, insert, or delete statement affected an unexpected
> number of rows (0). Entities may have been modified or deleted since
> entities were loaded. See
> http://go.microsoft.com/fwlink/?LinkId=472540 for information on
> understanding and handling optimistic concurrency exceptions."}

The main program routine is:

class Program
{
    static void Main( string[] args )
    {
        var ctx = new SchoolContext();

        Student stud = new Student();
        stud.StudentID = 0;
        stud.Height = 0.2;
        stud.StudentName = "New Student";

        ctx.Students.Add(stud);

        ctx.SaveChanges();
    }
}

The DbContext is:

public class SchoolContext : DbContext
{
    public SchoolContext()  : base("name=SchoolContext")
    {
    }

    public virtual DbSet<Student> Students { get; set; }
}

and the model is:

public class Student
{
    public Student()
    {
    }

    public int StudentID { get;set; }
    public string StudentName { get;set; }
    public double Height { get;set; }
}

My app.config is:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
    <entityFramework>
        <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF6" />
        <providers>
            <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
        </providers>
    </entityFramework>
    <connectionStrings>
        <add name="SchoolContext" providerName="MySql.Data.MySqlClient" connectionString="server=localhost;port=3306;database=test;uid=root;password=tree"/>
    </connectionStrings>
</configuration>

My packages.config file is:

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="EntityFramework" version="6.1.3" targetFramework="net40" />
</packages>

My database table is defined as:

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `Height` double(10,5) DEFAULT NULL,
  `StudentId` int(2) DEFAULT NULL,
  `StudentName` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The exception occurs in the main program at the line: ctx.SaveChanges();

There is no additional code that modifies my entries after having been loaded, so this exception makes no sense. The only files in my project are the ones posted above.

Does anyone know why this occurs and how I should fix it?

A code snippet would be very helpful.

Thanks.

I'm guessing you've defined height to be something other than a double in mysql. Please add your table definition to your question. — Robert McKee
– Robert McKee, Commented Jul 22, 2015 at 20:12
At first I thought this was the case too, but it turns out that height is defined as double. The error still occurs. — guerrillacodester
– guerrillacodester, Commented Jul 22, 2015 at 20:32
double(2,0) doesn't make any sense. That's a floating point number that is accurate to 2 places, and has no decimal. It's basically a badly encoded byte. Refactor into decimal (5,2) would likely be appropriate here. — Robert McKee
– Robert McKee, Commented Jul 23, 2015 at 16:04
You are correct. I changed it. This was a typo. Other than that, it works fine after having defined a primary key. — guerrillacodester
– guerrillacodester, Commented Jul 23, 2015 at 18:33

guerrillacodester · Accepted Answer · 2015-07-23 06:59:15Z

1

The DbUpdateConcurrencyException has been solved by editing the data table to make SutdentID a primary key:

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `Height` double(10,5) DEFAULT NULL,
  `StudentId` int(2) NOT NULL,
  `StudentName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`StudentId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Everything works as expected. Now if someone could explain just WHY this misleading exception should be raised because of the lack of a primary key, it would give me some closure...

Thanks.

answered Jul 23, 2015 at 6:59

guerrillacodester

5315 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert McKee Over a year ago

The easy answer is to tell you to look at the before and after SQL that gets generated. With a recent version of EntityFramework (assuming your dbcontext is defined controller wide and named _db), all you have to do is add in your controller's constructor, just add: _db.Database.Log = s => Debug.Write(s); then run your program before and after your change and see what the difference is in the SQL that got generated.

Robert McKee · Accepted Answer · 2015-07-23 16:09:45Z

double(2,0) doesn't make any sense. That's a floating point number that is accurate to 2 places, and has no decimal. It's basically a badly encoded byte. Refactor into decimal (5,2) would likely be appropriate here.

It is likely working with the primary key because it is then using the primary key to find the record to update rather than the combination of all the old values. Your use of double(2,0) is causing an issue where the precision isn't roundtripping correctly.

It's as if you are trying to store 152.999999999 in the database, and when retrieved, it is getting rounded to 153. Then when you try and update, it requests the record with height 153 to be updated, which the database then can't find.

This type of problem isn't that uncommon with types that aren't exact (which floating points aren't), and also with MySql because it does a really bad job of enforcing the datatype you've specified -- often just shoving what you give it into the database even if the type isn't correct. But then when you use it in an application that actually does respect the type, some precision is lost and it'll differ from the value that MySql has.

Collectives™ on Stack Overflow

How to solve DbUpdateConcurrencyException for EntityFramework with MySql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related