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

Source: Internet
Author: User
Tags mysql connection string

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.