I'm working on a management system project in ASP.NET Core and I've encountered an issue with saving a related entity to the database. I'll describe the problem using a simplified example. I have two classes visible in the following screenshots. I also have an endpoint that is supposed to create a new instance of Car and add it to the Cars collection in the User class, then save the changes to the database. However, when I call this endpoint, I get the following exception:
Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded.
I checked the Microsoft documentation, and it seems like my code doesn't differ from the examples, so if someone could explain where the issue lies, I would greatly appreciate it.
Models:
public class User : BaseEntity
{
public string FirstName { get; protected set; }
public string LastName { get; protected set; }
public ICollection<Car> Cars { get; set; }
public User(){}
public User(Guid id, string firstName, string lastName)
{
Id = id;
FirstName = firstName;
LastName = lastName;
}
public void AddCar(Car car)
{
Cars.Add(car);
}
}
public class Car : BaseEntity
{
public string Model { get; protected set; }
public DateOnly Year { get; protected set; }
public Guid UserId { get; set; }
public User User { get; protected set; }
public Car() {}
public Car(Guid id, string model, DateOnly year)
{
Id = id;
Model = model;
Year = year;
}
}
Context:
public class AppDbContext : DbContext
{
public AppDbContext() { }
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
public DbSet<User> Users { get; set; }
public DbSet<Car> Cars { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.HasDefaultSchema("test");
modelBuilder.Entity<User>(entity =>
{
entity.HasKey(x => x.Id);
entity.HasMany(u => u.Cars)
.WithOne(c => c.User)
.HasForeignKey(c => c.UserId);
});
modelBuilder.Entity<Car>(entity =>
{
entity.HasKey(c => c.Id);
entity.HasOne(c => c.User)
.WithMany(u => u.Cars)
.HasForeignKey(c => c.UserId);
});
modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
}
}
Endpoint:
[HttpPost("{userId}/add-car")]
public async Task<IActionResult> AddCar(Guid userId, [FromBody] AddCarRequest request)
{
await using (var context = new AppDbContext())
{
var car = new Car(Guid.NewGuid(), request.Model, request.Year);
var user = context.Users.Include(x => x.Cars).FirstOrDefault(x => x.Id == userId);
user.AddCar(car);
await context.SaveChangesAsync();
return Ok();
}
}
Exception:
Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See https://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlModificationCommandBatch.ThrowAggregateUpdateConcurrencyExceptionAsync(RelationalDataReader reader, Int32 commandIndex, Int32 expectedRowsAffected, Int32 rowsAffected, CancellationToken cancellationToken)
at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlModificationCommandBatch.Consume(RelationalDataReader reader, Boolean async, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at EFTest.Controllers.DefaultController.AddCar(Guid userId, AddCarRequest request) in S:\Repos\EFTest\EFTest\Controllers\DefaultController.cs:line 38
at EFTest.Controllers.DefaultController.AddCar(Guid userId, AddCarRequest request) in S:\Repos\EFTest\EFTest\Controllers\DefaultController.cs:line 40
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
I compared my code with Microsoft's documentation, and in my opinion, everything seems correct.
using (var context = new BloggingContext())
{
var blog = context.Blogs.Include(b => b.Posts).First();
var post = new Post { Title = "Intro to EF Core" };
blog.Posts.Add(post);
context.SaveChanges();
}
Queries executed by EF:
info: 13.09.2024 20:51:02.616 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (20ms) [Parameters=[@__userId_0='42d2ac6e-e663-41a9-830e-3e49b38634c9'], CommandType='Text', CommandTimeout='30']
SELECT t."Id", t."CreatedAt", t."FirstName", t."LastName", t."UpdatedAt", c."Id", c."CreatedAt", c."Model", c."UpdatedAt", c."UserId", c."Year"
FROM (
SELECT u."Id", u."CreatedAt", u."FirstName", u."LastName", u."UpdatedAt"
FROM test."Users" AS u
WHERE u."Id" = @__userId_0
LIMIT 1
) AS t
LEFT JOIN test."Cars" AS c ON t."Id" = c."UserId"
ORDER BY t."Id"
info: 13.09.2024 20:51:03.025 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
Executed DbCommand (23ms) [Parameters=[@p5='7c87ad78-534b-4e1c-a472-653eab8a0fb9', @p0='0001-01-01T00:00:00.0000000' (DbType = DateTime), @p1='TEST' (Nullable = false), @p2='0001-01-01T00:00:00.0000000' (DbType = DateTime), @p3='42d2ac6e-e663-41a9-830e-3e49b38634c9', @p4='01/01/2000' (DbType = Date)], CommandType='Text', CommandTimeout='30']
UPDATE test."Cars" SET "CreatedAt" = @p0, "Model" = @p1, "UpdatedAt" = @p2, "UserId" = @p3, "Year" = @p4
WHERE "Id" = @p5;
You are getting user entity, which then is tracked by the db context and is in EntityState.Modified state.
Then you create new car entity, but you provide ID for it - based on that EF will assume that it is also existing entity and will try to UPDATE instead of CREATE it!
So, roughly speaking, it will do query UPDATE ... WHERE ID={id created by you} and this query will update 0 rows, as there will be no entity with such ID, while EF expects to affect 1 row. Thus the error.
If you look at attached example, there the key is not assigned as well.
To correct that, omit assigning new ID (as far as I remember, empty GUID will work as well):
var car = new Car(Guid.Empty, request.Model, request.Year);