.NET / EF Core CRUD The database operation was expected to affect 1 row(s), but actually affected 0 row(s);

Question

I'm creating a new project trying to use Generics as much as possible, but I've encountered an issue when doing HttpPut and using EF Core.

I'm using SQL Server in a container to store temporary data.

I keep getting the "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've tried using _context.Update(entity) as well and it didn't work... I'm not sure what's going on... Maybe it's because I'm using a generic Service and Repo access?

Here's the code:

Controller:

    [HttpPut("{id}")]
    public async Task<IActionResult> UpdateMedicalRecord(int id, MedicalRecord medicalRecord)
    {

        if (_mediTrackService is null)
        {
            throw new Exception("Service not found");
        }

        try
        {
            var data = await _mediTrackService.GetDataByIdAsync(id);
            if (data is null)
            {
                return NotFound();
            }

            await _mediTrackService.UpdateExistingAsync(medicalRecord);
            return Created();
        }
        catch (Exception e)
        {
            throw new Exception($"{e.Message}");
        }
    }

Service:

    public async Task UpdateExistingAsync(T entity)
    {
        try
        {
            await _mediTrackRepo.UpdateExistingAsync(entity);
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
    }

Repository:

    public async Task UpdateExistingAsync(T entity)
    {
        _context.Entry(entity).State = EntityState.Modified;
        try
        {
            await _context.SaveChangesAsync();
        }
        catch (DbUpdateConcurrencyException ex)
        {

            throw new DbUpdateConcurrencyException(ex.Message);
        }

    }

Terrible, abtraction over DbSet and nother abstraction over abstraction. Why? Remove repository layer, if you have services. In service introduce UpdateMedicalRecord(int id, MedicalRecord medicalRecord) { var existing = _context.MedicalRecords.Find(Id); _context.Entry(existing).CurrentValues.SetValues(medicalRecord); _context.SaveChanges() } — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Jul 22, 2024 at 20:32
I'm using Services inside my Application layer and Repository inside the Infrastructure layer (trying to use Clear Arch), that's why I have these many abstractions... Not a fan either... — Marcio
– Marcio, Commented Jul 22, 2024 at 20:53
So, use Microsoft.EntityFrameworkCore package it is just super abstract repository layer with UoW. It is abstraction over databases, for sure If you do not plan in nearest future use other ORM. — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Jul 22, 2024 at 21:29

Steve Py · Accepted Answer · 2024-07-22 21:47:11Z

Generics apply when the classes they wrap are 100% interchangeable. A generic wrapper can work for simple entities but will fail when you give it entities that contain references to other entities. (Aggregates) For instance if you pass it a MedicalRecord entity that has a reference to a Patient entity, or contains a collection of Tags, etc. At this point you cannot rely on simply calling the DbContext.Update() method. This can result with various exceptions or unexpected behaviour like inserting duplicate rows. (same data, new IDs) Making a generic UpdateExisting wrapper cannot apply the same behavior to a simple entity with no references as dealing with a complex entity. Hence the advice, do not use a Generic method.

My advice when it comes to EF is to avoid passing detached entities around entirely. Yes, you can build systems passing detached entities but you need to take extra care when dealing with aggregates as well as dealing with potential tracking issues when you start re-attaching entities and references to the DbContext. Entities are also relative heavyweights when it comes to the needs of a view. You need to pass a complete entity around where a view might only care about a subset of values, and if an entity comes back from a view, it had better be complete or risk invalidating data state or causing errors on the round trip. Instead I recommend projecting to view models or DTOs and leaving entities to be solely concerned with data domain concerns. If you get in the habit of passing view models back and forth between domain and consumers (Views, APIs, etc.) then you have an optimized data structure for transport and there is no confusion or assumptions about how to deal with the data domain state.

There can be a perceived optimization in the idea of not having to do another round trip to the database, simply pass around entities and call Update. However, this is a more complicated scenario when dealing with related references, as well as posing a risk for unintended alterations. Fetching entities by ID to perform an update is an extremely efficient call, assures only values you intend to be updated get changed, and allows for assertions to preemptively handle stale data. (checking concurrency timestamps/row versions)

Thank you, you are absolutely correct! I also have "Patient" and "Physician" entities and they do not have other references, but using Generics for everything made the whole code go somewhat crazy... I will use DTOs for sure.

Michał Turczyn · Accepted Answer · 2024-07-27 07:12:09Z

0

One issue might be that your entity has changed ID as well.

You validate ID passed separately in another parameter.

New ID means entity should be created and setting its state to Modified can cause such error.

Minimal example using minimal API:

app.MapPut("/update-random-movie-with-exception", async (ExampleWebApiContext dbContext, CancellationToken cancellationToken) =>
{
    // If we generate new id, but mark entity as modified
    var newMovie = new Movie()
    {
        Id = Guid.NewGuid(),
        Title = $"Movie create at {DateTimeOffset.UtcNow:yyyy-MM-dddd hh:mm:ss.fff}",
        ReleaseYear = 1900 + Random.Shared.Next(0, 100),
    };

    dbContext.Entry(newMovie).State = EntityState.Modified;

    await dbContext.SaveChangesAsync(cancellationToken);
});

edited Jul 27, 2024 at 7:12

answered Jul 22, 2024 at 21:13

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Collectives™ on Stack Overflow

.NET / EF Core CRUD The database operation was expected to affect 1 row(s), but actually affected 0 row(s);

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related