Efcore: EF Core Update Error

[Edward-Zhou]

I want to update a existing record by EntityFrameworkCore, but it throws error below:

fail: Microsoft.EntityFrameworkCore.DbContext[1]
      An exception occurred in the database while saving changes.
      Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
         at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyException(Int32 commandIndex, Int32 expectedRowsAffected, Int32 rowsAffected)
         at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.<ConsumeResultSetWithoutPropagationAsync>d__6.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.<ConsumeAsync>d__2.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync>d__32.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__10.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__6`2.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__54.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__52.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.DbContext.<SaveChangesAsync>d__35.MoveNext()
Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyException(Int32 commandIndex, Int32 expectedRowsAffected, Int32 rowsAffected)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.<ConsumeResultSetWithoutPropagationAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.<ConsumeAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync>d__32.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__6`2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__54.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__52.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.DbContext.<SaveChangesAsync>d__35.MoveNext()

Steps to reproduce

```c#
private static async Task InsertTestData(IServiceProvider serviceProvider)
{
//add users
var users = GetUsers();
await AddOrUpdateAsync(serviceProvider,u=>u.Id,users);
}
public static IEnumerable GetUsers()
{
var users = new ApplicationUser[] {
//new ApplicationUser{ Id="1", UserName="Tom" },
//new ApplicationUser{ Id="2",UserName="Jim"},
new ApplicationUser{ Id="3",UserName="Tony"}
};
return users;
}

    private static async Task<IEnumerable<TEntity>> AddOrUpdateAsync<TEntity>(IServiceProvider serviceProvider,
        Func<TEntity,object> propertyToMatch, IEnumerable<TEntity> entities) where TEntity : class
    {
        //query existing data in a separate context, and then attach them as modified
        List<TEntity> existingData;
        using (var serviceScope = serviceProvider.GetRequiredService<IServiceScopeFactory>().CreateScope())
        {
            var db = serviceScope.ServiceProvider.GetService<ApplicationDbContext>();
            existingData = db.Set<TEntity>().ToList();
        }
        var resultList = new List<TEntity>();
        using (var serviceScope = serviceProvider.GetRequiredService<IServiceScopeFactory>().CreateScope())
        {
            var db = serviceScope.ServiceProvider.GetService<ApplicationDbContext>();
            foreach (TEntity item in entities)
            {
                var state = existingData.Any(data => propertyToMatch(data).Equals(propertyToMatch(item))) ? EntityState.Modified : EntityState.Added;
                if (state == EntityState.Added)
                {
                    await db.Set<TEntity>().AddAsync(item);
                    resultList.Add(item);
                }
                else
                {
                    db.Entry(item).State = state;
                    //db.Set<TEntity>().Update(item);
                    resultList.Add(item);
                }
            }
            await db.SaveChangesAsync();
            return resultList;
        }
    }

```
ApplicationUser with Id=3 already exist, I want to update it with UserName.

Further technical details

IDE:VS2017

[ajcvickers]

@Edward-Zhou If seems likely that this:
C# propertyToMatch(data).Equals(propertyToMatch(item)))
is returning true for entities that have not already been saved. This then causes them to be put into a Modified state, resulting in an UPDATE statement being sent to the database, but that update does not match an existing entity and hence the exception is thrown.

[Edward-Zhou]

@ajcvickers Thanks.
I think it is not related with propertyToMatch(data).Equals(propertyToMatch(item))) which is used to check whether the entity already exists, if not, add, if exists, update.
And, I made a test with below code, this issue still exist.

        private static async Task<IEnumerable<TEntity>> AddOrUpdateAsync<TEntity>(IServiceProvider serviceProvider,
            Func<TEntity,object> propertyToMatch, IEnumerable<TEntity> entities) where TEntity : class
        {
            //query existing data in a separate context, and then attach them as modified
            List<TEntity> existingData;
            using (var serviceScope = serviceProvider.GetRequiredService<IServiceScopeFactory>().CreateScope())
            {
                var db = serviceScope.ServiceProvider.GetService<ApplicationDbContext>();
                existingData = db.Set<TEntity>().ToList();
            }
            var resultList = new List<TEntity>();
            using (var serviceScope = serviceProvider.GetRequiredService<IServiceScopeFactory>().CreateScope())
            {
                var db = serviceScope.ServiceProvider.GetService<ApplicationDbContext>();
                foreach (TEntity item in entities)
                {
                    db.Entry(item).State = EntityState.Modified;
                    //var state = existingData.Any(data => propertyToMatch(data).Equals(propertyToMatch(item))) ? EntityState.Modified : EntityState.Added;
                    //if (state == EntityState.Added)
                    //{
                    //    await db.Set<TEntity>().AddAsync(item);
                    //    resultList.Add(item);
                    //}
                    //else
                    //{
                    //    db.Entry(item).State = state;
                    //    //db.Set<TEntity>().Update(item);
                    //    resultList.Add(item);
                    //}
                }
                await db.SaveChangesAsync();
                return resultList;
            }
        }

[ajcvickers]

@Edward-Zhou I am not able to reproduce this issue with the code you have posted. Could you try to post a full code listing or project that reproduces what you are seeing?

[Edward-Zhou]

@ajcvickers
Sorry for late response.
For the project, please check https://github.com/Edward-Zhou/AngularWithCore.
Steps:Build and Run the Project.
This line DbInitializer.Initialize(app.ApplicationServices).Wait(); will call the piece code.
Note, you need to run first to generate database and insert code, then edit the user records like changing user name, then run the project again, now, it should run update and will produce this error.
If there is any problem, please let me know.

[ajcvickers]

@Edward-Zhou Still can't get this to reproduce. Or at least, only in one case--with the code in this comment, which has the existingData check removed, it will fail if the database doesn't have the existing data, but this is expected. With the check back in, the code correctly puts new objects into the Added state and existing objects into the Modified state.

Is there something special I need to do to try to get this to show the issue?

[Edward-Zhou]

@ajcvickers
Please follow below steps to reproduce this issue.

1. Clone my project,
2. Run my project, if the database does not create, please run add migration and update database
   3.After running project, it will run below code and insert new test record.
   private static async Task InsertTestData(IServiceProvider serviceProvider) { //add users var users = GetUsers(); await AddOrUpdateAsync(serviceProvider,u=>u.Id,users); } public static IEnumerable<ApplicationUser> GetUsers() { var users = new ApplicationUser[] { //new ApplicationUser{ Id="1", UserName="Tom" }, //new ApplicationUser{ Id="2",UserName="Jim"}, new ApplicationUser{ Id="3",UserName="Tony"} }; return users; }
   4.Check whether Tony user with Id 3 exist in database
   5.Change the above code to new user.
   new ApplicationUser{ Id="3",UserName="Test"}
   6.It will produce error which should update the Tony user to Test user.
   7.If you did not get error, I assume it is related with async, and I suggest you check database whether the record is udpate.
   If you still could not reproduce, please share me your steps.

[ajcvickers]

@Edward-Zhou Can you explain what you mean by this. "If you did not get error, I assume it is related with async,"?

[Edward-Zhou]

@ajcvickers Have you tried? await db.SaveChangesAsync(); this line save changes async. If there is any error in this step, will it break application?

[ajcvickers]

@Edward-Zhou When you say, "If you did not get error" I assume you mean that there is some situation where you think (or even have seen) that the error doesn't happen. And when you say, "I assume it is related with async" I assume you mean that you think that if the error doesn't happen is is related in some way with async. Can you provide more details on what you mean by this? In what situations have you not seen or you think I may not see the error? And in what way are those situations related to async?

Basically, there is nothing obvious in the code you have posted that is wrong, and I have not been able to get it to fail on my machine. Would it be possible to trim things down to a console app where you are still seeing the error? That way we can eliminate some of the variables here and maybe both figure out why you are seeing the issue but I am not, and then also figure out what can be done to fix it.

[Edward-Zhou]

Can you update the records with current code? I want to update the record if it exist, can you update it my current code?
Could you get the record update to Tony to Test with below step?
1.Clone my project,
2.Run my project, if the database does not create, please run add migration and update database
3.After running project, it will run below code and insert new test record.
private static async Task InsertTestData(IServiceProvider serviceProvider) { //add users var users = GetUsers(); await AddOrUpdateAsync(serviceProvider,u=>u.Id,users); } public static IEnumerable GetUsers() { var users = new ApplicationUser[] { //new ApplicationUser{ Id="1", UserName="Tom" }, //new ApplicationUser{ Id="2",UserName="Jim"}, new ApplicationUser{ Id="3",UserName="Tony"} }; return users; }
4.Check whether Tony user with Id 3 exist in database
5.Change the above code to new user.
new ApplicationUser{ Id="3",UserName="Test"}
6.It will produce error which should update the Tony user to Test user.
7.If you did not get error, I assume it is related with async, and I suggest you check database whether the record is udpate.

[Edward-Zhou]

@ajcvickers Could you reproduce this issue?

[smitpatel]

I was able to reproduce this issue. The error here is due to ApplicationUser.ConcurrencyStamp property.
ApplicationUser in identity uses ConcurrencyStamp of type Guid for concurrency. When creating new class it sets the value to NewGuid(). When you create new ApplicationUser like that and set its state to Modified EF Core does not have data about what was ConcurrencyStamp in database. Hence it will use whatever is the value set on the item (which will be NewGuid()) Since this value differ from value in database and it is used in where clause of update statement, exception is thrown that 0 rows modified when expected 1.

When updating entity with concurrency token you cannot create new object and send update directly. You must retrieve record from database (so that you have value of ConcurrencyStamp) then update the record and call SaveChanges. Since the ApplicationUser.ConcurrencyStamp is client side concurrency token you also need to generate a NewGuid() while updating the record. So it can update the value in database.

For more information on how to deal with ConcurrencyStamp in identity, look at code here https://github.com/aspnet/Identity/blob/f555a26b4a554f73eea70b4b34fca823fab9a643/src/Microsoft.AspNetCore.Identity.EntityFrameworkCore/UserStore.cs#L175
In above code user is database retrieved record which had some of the properties modified. It described precise way to update the record.

For general questions around how to work with client generated concurrency tokens, use stackoverflow.