C# - Entity Framework Core - Database operation expected to affect 1 row(s) but actually affected 0 row(s)

Question

I'm building an n-tier web app in ASP.NET Core 3.1 that uses Entity Framework to interact with a SQL Server database. Everything works fine except for updating an entity twice without refreshing in between both requests.

So the first time I read and entity from the database and update it, it works perfectly. When I try to update the entity immediately after, I get the following exception:

Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: 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...

I'm using the generic repository pattern in combination with asynchronous code.

An overview of my code:

GenericRepository.cs:

public class Repository<T> : IRepository<T> where T : class
{        
    protected readonly DataContext _context;
    protected DbSet<T> _entities;
    
    public Repository(DataContext context)
    {
        this._context = context;
        _entities = context.Set<T>();
    }
}

MemberRepository.cs:

public class MemberRepository : Repository<Member>, IMemberRepository
{    
    public MemberRepository(DataContext context) : base(context)
    {
        //
    }

    public async override Task<Member> SelectByIdAsync(int id)
    {
        return await this._entities
                    .Where(m => m.Id == id)
                    .Include(m => m.Addresses)
                    .Include(m => m.MemberHobbies)
                    .Include(m => m.Car)
                    .Include(m => m.EmailAddresses)
                    .FirstOrDefaultAsync();
        
        //return await this._context.Members.Where(m => m.Id == id).Include(m => m.Addresses).Include(m => m.MemberHobbies).Include(m => m.Car).Include(m => m.EmailAddresses).FirstOrDefaultAsync();
    }

    public async override Task UpdateAsync(Member member)
    {
        this._context.Members.Update(member);
        //this._context.Set<Member>().Update(member);
        await this._context.SaveChangesAsync();
    }
}

startup.cs:

services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
services.AddScoped<IMemberRepository, MemberRepository>();

I have tried to change the way I read or update my entity (see the commented out lines), but those give the same result. Also: I have other entities which work perfectly, the only difference is that the Member class has multiple foreign keys (see .Include(...))

Might it have anything to do with improper use of asynchronous functions? Maybe because I'm not returning the updated Member and thus next time I execute an update I'm sending a now out of date member?

Also: I found this answer, but it makes no sense to me at all

Thanks!

Answer 1

I use the generic code like this for update:

public virtual async Task<T> UpdateAsync(T t)
        {
            if (t == null) return null;

            T exist;

            try
            {
                exist = await Context.Set<T>().FindAsync(t.Id);
                if (exist == null)
                {
                    t.ErrorMessage = "Can't find item to update";
                    return t;
                }
                Context.Entry(exist).CurrentValues.SetValues(t);
                var result = await Context.SaveChangesAsync();
                if (result == 0) t.ErrorMessage = "Can't saved item";
            }
            catch (Exception ex)
            {

                t.ErrorMessage = ex.Message;
                return t;
            }


            return exist;
        }

Or you can try this code for Member class:

public async Task UpdateAsync(Member member)
{
 try
 {
    var exist = await _context.Members.Where(i.Id=member.Id).FirstOrDefaultAsync();
   if (exist == null) ....errorMessage = "Can't find item to update";
 
   Context.Entry(exist).CurrentValues.SetValues(member);
   var result = await Context.SaveChangesAsync();

   if (result == 0) ....errorMessage = "Can't save item";

 }
 catch (Exception ex)
 {
    ...errorMessage = ex.Message;
 }
}

Answer 2

EF keeps track of your data. Here's what you do:

1. Get two copies of the same entry;
2. Change the former copy;
3. Update the change for former copy;
4. Change the latter copy, which kept the track of the entry's state prior the 3rd step;
5. Update the change made by the latter copy, but since this change reflects to the former state of an entry - you get that error;

Here's what you should do instead:

1. Add an extra layer to your business objects that will carry the states around;
2. Get a current state of your database entry upon receiving the update request;
3. Apply the changes to your DB entry;
4. Save;

Example:

// DB entry
public class Member
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// DTO for carrying around
public class MemberForNameChangeDto
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Repo
{
    public Task<bool> UpdateMemberName(MemberForNameChangeDto memberForNameChange)
    {
        // _context.Members is DbSet<Member>
        var memberEntry = _context.Members.Find(memberForNameChange.Id);
        // null checks, verifications, etc
        memberEntry.Name = memberForNameChange.Name;
        var result = await _context.SaveChangesAsync();

        return result > 0;
    }
}