Database operation expected to affect 1 row(s) but actually affected 0 row(s)

Question

I'm trying to insert records in two tables, but getting the exception. Could you please help me to resolve the issue.

First I tried the below code.

await _testRepository.InsertAsync(test);
await _xyzRepository.InsertAsync(xyz);

Then I tried this code, But nothing is working for me.

try
{
   var test = new Test();

   using (var uow = _unitOfWorkManager.Begin(TransactionScopeOption.RequiresNew))
   {
      int? tenantId = _unitOfWorkManager.Current.GetTenantId();
      using (_unitOfWorkManager.Current.SetTenantId(tenantId))
      {
         await _testRepository.InsertAsync(test);

         var xyz = new XYZ();
         await _xyzRepository.InsertAsync(xyz);
         await _unitOfWorkManager.Current.SaveChangesAsync();
         await uow.CompleteAsync();
      }
   }
}
catch (Exception ex)
{
   throw new UserFriendlyException(ex.Message);
}

Exception

Message:

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.

stack trace:

at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyException(Int32 commandIndex, Int32 expectedRowsAffected, Int32 rowsAffected) at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.d__4.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.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.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.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.d__7`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.d__61.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.d__59.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.d__48.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 Abp.EntityFrameworkCore.AbpDbContext.d__49.MoveNext() in D:\Github\aspnetboilerplate\src\Abp.EntityFrameworkCore\EntityFrameworkCore\AbpDbContext.cs:line 214

INFO 2018-04-11 13:59:53,439 [3 ] ore.Mvc.Internal.ControllerActionInvoker - Executing action method MyCompany.MyProject.AdditionalMasterData.Tests.TestsAppService.CreateOrEdit (MyCompany.MyProject.Application) with arguments ([CreateOrEditTestDto ]) - ModelState is Valid WARN 2018-04-11 14:01:48,396 [4 ] Mvc.ExceptionHandling.AbpExceptionFilter - 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. Abp.UI.UserFriendlyException: 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 MyCompany.MyProject.AdditionalMasterData.Tests.TestsAppService.d__7.MoveNext() in C:\Repo\MyProjectVenues\aspnet-core\src\MyCompany.MyProject.Application\AdditionalMasterData\Tests\TestsAppService.cs:line 205 --- 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 MyCompany.MyProject.AdditionalMasterData.Tests.TestsAppService.d__6.MoveNext() in C:\Repo\MyProjectVenues\aspnet-core\src\MyCompany.MyProject.Application\AdditionalMasterData\Tests\TestsAppService.cs:line 170 --- 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.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__12.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.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__10.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context) at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__14.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.AspNetCore.Mvc.Internal.ResourceInvoker.d__23.MoveNext()

Update

I have got the root cause of the issue.

So basically I have an insert trigger on Entity2 and When I have commented the query inside this trigger and then its working fine.

There are approximately 10 queries in this trigger and it's very hard to know which one is causing the problem. So could you please let me know how to debug this trigger?

Answer 1

Make sure in your repository function InsertAsync you are not calling AddAsync unless your are using the Microsoft.EntityFrameworkCore.Metadata.SqlServerValueGenerationStrategy.SequenceHiLo generator. AS NOTED IN the Docs. AddAsync

Answer 2

I had a similar problem. I used EF Core. I was helped by the following change for my code.

context.Entry(user).State = EntityState.Added; // added row
this.context.Users.Add(user);
this.context.SaveChanges();

UPD: Sorry, problem has been solved by adding a Identity attribute for User.Id

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

Answer 3

For my case, the problem caused when I tried to pass to Update() method an entity that didn't exist in database.

Answer 4

Make sure the generated SQL-query can actually reach the targeted row. In my case the query looked like this:

UPDATE [Users] SET .....
WHERE [Id] = @p34 AND [ConcurrencyStamp] = null;

but 'ConcurrencyStamp' contained a not-null value so there was nothing to update.

Answer 5

Issue with my code was, i was setting primary key value for tables explicitly, it's not db generated, but efcore was not aware about this.. so finally i need to write (ValueGeneratedNever)

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            //TODO: on add any entity add here the same
            modelBuilder.Entity<AdminUser>().Property(e => e.AdminUserId).ValueGeneratedNever();
            modelBuilder.Entity<AdminUserLogInLog>().Property(e => e.AdminUserLogInLogId).ValueGeneratedNever();
            ...........
         }

Generic method for all table

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        
        foreach (var item in modelBuilder.Model.GetEntityTypes())
        {
            var p = item.FindPrimaryKey().Properties.FirstOrDefault(i=>i.ValueGenerated!=Microsoft.EntityFrameworkCore.Metadata.ValueGenerated.Never);
            if (p!=null)
            {
                p.ValueGenerated = Microsoft.EntityFrameworkCore.Metadata.ValueGenerated.Never;
            }

        }
    }

Answer 6

Also you can check if your table have PK (primary key) identity option enabled.

Answer 7

If anyone arrives here with a background the same as mine, I had this error for an Add when I had a DateTime column as PK and this column was configured as .HasDefaultValueSql("getdate()").

I had to use the profiler to see that EFCore was sending NULL value for this column and trying to compare to the newly generated date:

exec sp_executesql N'SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([Current_Nbr] nvarchar(450), [Db_Date] datetime2, [_Position] [int]);
MERGE [Current_Moves] USING (
VALUES (@p0, @p1, @p2, @p3, 0)) AS i ([Current_Nbr], [Despatch_Number], [Current_Loc], [Operator], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Current_Nbr], [Despatch_Number], [Current_Loc], [Operator])
VALUES (i.[Current_Nbr], i.[Despatch_Number], i.[Current_Loc], i.[Operator])
OUTPUT INSERTED.[Current_Nbr], INSERTED.[Db_Date], i._Position
INTO @inserted0;

SELECT [t].[Db_Date] FROM [Current_Moves] t
INNER JOIN @inserted0 i ON ([t].[Current_Nbr] = [i].[Current_Nbr]) AND ([t].[Db_Date] = [i].[Db_Date])
ORDER BY [i].[_Position];

',
N'@p0 nvarchar(450),@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000)',
@p0=N'AAAAA',@p1=NULL,@p2=N'LOC_123',@p3=N'xxxxx'

I haven't actually fixed that error, but I was able to make it work by setting the DateTime column's value on C#, instead of expecting the database to do that.

Answer 8

Like another user, my problem is caused by a rowversion in SQL Server (called byte[] ChangeVersion in our model). Entity Framework was appending where ChangeVersion = ... as part of the SQL queries. However, the value Entity Framework used for the ChangeVersion was default (0). Entity Framework has decided that the ChangeVersion property should not be populated from the database with the following:

var deleteCompany = dbContext.Company.FirstOrDefault(x => x.CompanyId == cmvm.DeleteCompanyId.Value);
// at this point, deleteCompany.ChangeVersion = 0 but other fields are populated.
dbContext.Company.Remove(deleteCompany);
dbContext.SaveChanges(); // error expected 1 row but 0 affected

The model builder looks like

entity.Property(e => e.ChangeVersion).HasColumnName("ChangeVersion")
                    .IsRequired()
                    .IsRowVersion();

Changing the original lookup query to AsNoTracking fixes the issue. This works without error

// Call this AsNoTracking. Otherwise Entity Frameworks throws out the ChangeVersion for some reason.
var deleteCompany = dbContext.Company.AsNoTracking().FirstOrDefault(x => x.CompanyId == cmvm.DeleteCompanyId.Value);
dbContext.Entry(deleteCompany).State = EntityState.Deleted;
dbContext.Company.Remove(deleteCompany);
dbContext.SaveChanges();

Answer 9

I ran into this same problem with a SQLite database, but with updates instead of inserts. Calling SaveChangesAsync instead of SaveChanges was the solution.