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?

All code in this question obscures what actually happens in Entity Framework. And in the end it turns into a completely different question. — Gert Arnold, Oct 31 '20 at 20:45

SVPopov · Answer 1 · 2019-03-28T07:25:19.720

14

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)]

edited Mar 28 '19 at 07:25

answered Mar 28 '19 at 07:03

SVPopov

181
2
7

second one fixed my issue, thanks! – Mr Giggles Nov 22 '22 at 14:55

score 10 · Accepted Answer · answered Apr 13 '18 at 20:16

10

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

answered Apr 13 '18 at 20:16

Robert Raboud

1,004
8
7

Thank you very much. I also suspected this error but there was no basis, but actually, this way fixed the error Database operation expected to affect 1 row(s) but actually affected 0 row(s) – Tang Thanh Tam Nov 09 '22 at 02:45

score 8 · Answer 3 · answered Oct 30 '19 at 15:39

8

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

answered Oct 30 '19 at 15:39

VahidShir

2,066
2
17
27

score 8 · Answer 4 · answered May 08 '21 at 15:01

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

        }
    }

score 3 · Answer 5 · answered Oct 31 '20 at 19:51

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.

score 1 · Answer 6 · answered May 25 '20 at 08:32

1

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

answered May 25 '20 at 08:32

Vasil Valchev

5,701
2
34
39

score 1 · Answer 7 · answered Sep 22 '21 at 15:17

1

I find that this can be a binding issue on the primary key(s). Check your query string to make sure that all is binding correctly in your view and update action. (make sure a query string value is not overwriting an input value).

answered Sep 22 '21 at 15:17

Justin

474
4
14

score 0 · Answer 8 · answered Apr 26 '21 at 00:58

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.

BurnsBA · Answer 9 · 2021-07-15T01:30:07.183

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();

*For some reason,* -- It's important to get to the bottom of that because that's not standard EF behavior. Something in your code must cause `ChangeVersion` to be `0`. I don't think an unclear bug should lead to an answer. "Seems to be caused" and "seems to fix" is too tentative to be helpful. — Gert Arnold, Jul 14 '21 at 20:37

score 0 · Answer 10 · answered Jun 19 '23 at 01:29

In my case, using the SQL Profiler I noticed that the generated update query checks the RowVersion and the RowVersion was 1 step behind the actual value in DB and that caused no row to get updated.

The reason was that I called a stored procedure one step before loading the entity and that Stored Procedure was updating and increasing the RowVersion consequently but since it happened outside of EF change tracking system it wasn't aware that they should refresh the cached entity's RowVersion value. So to synchronise the EF with the DB RowVersion values you can either call :

dbContext.ChangeTracker.Clear();

or after loading the entity making sure that it has been refreshed:

dbContext.Entry(entity).ReloadAsync();

It depend which one is suitable for you.

emile keen · Answer 11 · 2023-07-09T08:11:10.500

This is an old question but since none of the answers above helped, I'll post my own solution. So In my app, I was trying to delete images from an existing product, and then upload new ones. The product Model contains a property a List, and after deleting/adding new images for this product, I couldn't save and got the error above. Instead of meddling with ModelBuilder etc, I found a simple solution to be;

Retrieve the product as Tracked Entity
Then Update the images
Save changes

This worked for me.

score 0 · Answer 12 · answered Aug 13 '23 at 16:09

Recently I've spent 3 days trying to solve the problem. I used EF 7.0. I discovered that the generated SQL script by EF doesn't work because the ConcurrencyStamp property has a different value. So I was forced to assign ConcurrencyStamp of my updated model manually before I transfer my model into the Update method. See the below fragment of the code:

  var user = _userManager.FindByNameAsync(model.UserName).Result;
            if (user == null)
            {
                return NotFound();
            }
            
            var editUser = _mapper.Map<EditProfileViewModel, User>(model);
            editUser.ConcurrencyStamp = user.ConcurrencyStamp;
            
            try
            {
                _userService.Update(editUser);

score -1 · Answer 13 · answered Oct 26 '18 at 14:55

-1

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

answered Oct 26 '18 at 14:55

pmichelbrink

37
1
6

Why does this fix the problem though? – zappa Apr 07 '20 at 08:53
1

I found that this did look like i'd fixed the problem from my UI but upon inspection of the database i hadn't actually written the data there. The cause for me turned out to be that i was explicitly setting the Id on an object which i then tried to add (insert) to the database but the object's model had a self generating Id anyway: ([Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public Guid Id { get; set; }) So EF was thinking 'they've given me an Id so this must be an update not an insert', hence the error. – zappa Apr 07 '20 at 10:03

score -1 · Answer 14 · answered Nov 17 '22 at 20:36

-1

For my case, I had updating 2 rows instead of 1.

I had less fields in PK group in entity building, than it was in DB..

entity.HasKey(e => new { e.field1, e field2, e.missedField3 }).HasName("My_Table_PK");

answered Nov 17 '22 at 20:36

GuRAm

758
1
7
22

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

14 Answers14

Linked