The bug that Entity Framework with MySQL provider updates the number of rows to 0

This article makes the problem easy to solve complicated. In most cases, set the use affected rows parameter in the MySQL connection string to true.

Author note.

 

When using the MySQL database as the provider of the Entity Framework, you may encounter the following problems:

The number of unexpected rows affected by the storage area update, insert, or delete Statement (0 ). Objects may be modified or deleted after being loaded. Refresh the objectstatemanager item.

The problem may occur in the following scenarios:

We usually encounter this problem. We open the editing page without changing any content, and click Save. The basic process is as follows:

   1: var detail=db.Table.FirstOrDefault(c=>c.id==1);

   2: detail.Content=model.Content;

   3: db.SaveChanges();

Because we have not changed any content, the content before the change is consistent with that after the change. The above error will be reported.

The reason is that the processing mechanism of MySQL is different from that of SQL Server. If the update content is consistent with that of the database, SQL Server still returns 1 affected rows, but MySQL considers the affected rows as 0.

Entity Framework should be subject to sqlserver by default. Therefore, if the number of affected items returned is 0, it is deemed that the update is incorrect.

There are many ways to deal with this problem. The most stupid thing is to judge before assigning a value, whether detail. Content is equal to model. content. If it is equal, no value is assigned.

However, because entityframework updates the state of this attribute when we assign values to the attribute to mark it as a status such as modified or deleted, we can have a more convenient way to control these updates.

We can use the savingchanges event in the context of entityframework to handle it before savechanges:

The basic principle is

If (the current object has been modified & the value after all attribute changes is the same as the value before the change ){

Not updated

}

The Code is as follows:

   1: public class EntityFrameworkFix

   2:     {

   3:         public static void SavingChanges(object sender, EventArgs e)

   4:         {

   5:             var context = sender as ObjectContext;

   6:             if (context == null) return;

   7:             var updatedEntites = context.ObjectStateManager.GetObjectStateEntries(EntityState.Modified);

   8:  

   9:             foreach (var ose in updatedEntites)

  10:             {

  11:                 var props = ose.GetModifiedProperties().ToList();

  12:                 int modifyCount = 0;

  13:                 int propCount = props.Count();

  14:                 for (int i = 0; i < propCount; i++)

  15:                 {

  16:                     var prop = props[i];

  17:                     var index = ose.OriginalValues.GetOrdinal(prop);

  18:                     if (ose.OriginalValues.GetValue(index).Equals(ose.CurrentValues.GetValue(index)))

  19:                     {

  20:                         modifyCount++;

  21:                     }

  22:                 }

  23:                 if (modifyCount == propCount)

  24:                 {

  25:                     context.Refresh(RefreshMode.StoreWins, ose.Entity);

  26:                 }

  27:             }

  28:         }

  29:     }

Then we add the following events when we declare context:

   1: db.SavingChanges += EntityFrameworkFix.SavingChanges;

In this way, you do not execute the values that are assigned but the values do not change.

In addition, entityframework4.1 was also released the previous day, but because entityframework4.1 no longer supports savingchanges, we need to use other methods to support it. We can implement this process using the override validateentity method in dbcontext.

   1: public partial class MyEntities : DbContext

   2: {

   3:     protected override System.Data.Entity.Validation.DbEntityValidationResult ValidateEntity(System.Data.Entity.Infrastructure.DbEntityEntry entityEntry, System.Collections.Generic.IDictionary<object, object> items)

   4:     {

   5:         int wrongCount = 0;

   6:         int allCount = 0;

   7:         if(entityEntry.State== System.Data.EntityState.Modified)

   8:         {

   9:             foreach(var name in entityEntry.OriginalValues.PropertyNames)

  10:             {

  11:                 var prop = entityEntry.Property(name);

  12:                 if (prop.IsModified)

  13:                 {

  14:                     allCount++;

  15:                     if (prop.OriginalValue.Equals(prop.CurrentValue))

  16:                         wrongCount++;

  17:                 }

  18:  

  19:             }

  20:             if (wrongCount == allCount)

  21:                 entityEntry.State = System.Data.EntityState.Unchanged;

  22:         }

  23:         return base.ValidateEntity(entityEntry, items);

  24:     }

  25: }