c#asp.net-coreef-code-firstentity-framework-coreaspnetboilerplate

Reputation: 1

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

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?

Upvotes: 24

Answers (16)

Omozuwa

Reputation: 1

In my case, because the data was migrated to a new server, there was a mismatch in data types, which was causing the concurrency error. As soon as I changed it to the data type that EF Core (RoleManager) was expecting, the error was resolved.

Upvotes: 0

Hosein Mohazebi

Reputation: 179

In my case, the issue was resolved because the 'ConcurrencyStamp' had been deleted from db. I added it back, and that fixed the problem in my ABP application using MongoDB.

Upvotes: 0

Rashit Nurzhanov

Reputation: 1

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

Upvotes: 0

emile keen

Reputation: 15

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.

Upvotes: 0

Spongebob Comrade

Reputation: 1568

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.

Upvotes: 1

GuRAm

Reputation: 777

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

Upvotes: -1

Justin

Reputation: 594

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

Upvotes: 1

BurnsBA

Reputation: 4929

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

Upvotes: 1

Moumit

Reputation: 9510

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

        }
    }

Upvotes: 13

Rick Wolff

Reputation: 777

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.

Upvotes: 0

Razor23 Donetsk

Reputation: 506

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.

Upvotes: 4

Vasil Valchev

Reputation: 5819

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

Upvotes: 1

VahidShir

Reputation: 2106

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

Upvotes: 9

SVPopov

Reputation: 331

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

Upvotes: 18

pmichelbrink

Reputation: 37

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

Upvotes: -1

Robert Raboud

Reputation: 1174

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

Upvotes: 12

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

Answers (16)

Related Questions