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

Question

I am using Sql Server on Linux with EC Core (2.1.0-preview1-final)

I am trying to update some data coming from a web Api service (PUT) request. The data (ticket) is passed correctly and gets deserialised into an object (ticket).

When I try to update, I use the following code:

public Ticket UpdateTicket(Ticket ticket)
        {
            using (var ctx = new SupportTicketContext(_connectionString))
            {
                ctx.Entry(ticket).State = EntityState.Modified;
                ctx.SaveChanges(); // <== **BLOWS UP HERE**
                var result = ctx.Tickets
                    .First(t => t.TicketId == ticket.TicketId);
                return result;

            }
        }

The code throws the following error:

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 am able to Insert and fetch from the database, no problem.
• If I restart Visual Studio, the error occurs usually on the second time I try to update ANY ticket (i.e. any other ticketId - it seems to be on the second and subsequent requests).
• The updates are unpredictably successful! Sometimes I can update another ticket and it goes through (even on the 3rd or susequent request)

• I have tried a number of modifications to the code, including

  ctx.Attach(ticket);

but this does not help.

How do I get it to update the database successfully? Any ideas on how to debug this? Logging seems to be difficult to set up.

Any ideas greatly appreciated.

Answer 1

There were two errors. First, I tried to call the Update() method without specifying a DbSet:

_applicationDbContext.Update(user);

Correct way:

_applicationDbContext.Users.Update(user);

The second error was trying to update a model that without first pulling it from the database:

    public bool UpdateUser(IdentityUser model)
    {
        _applicationDbContext.Users.Update(model);
        _applicationDbContext.SaveChanges();

        return true;
    }

Nope.

I first needed to retrieve it from the database, then update it:

    public bool UpdateUser(IdentityUser model)
    {
        var user = _applicationDbContext.Users.FirstOrDefault(u => u.Id == model.Id);

        user.PhoneNumberConfirmed = model.PhoneNumberConfirmed;

        _applicationDbContext.Users.Update(user);
        _applicationDbContext.SaveChanges();

        return true;
    }

Answer 2

Had such problem too, but the reason was a trigger I was using.

Changed the trigger type from "INSTEAD OF INSERT" to "AFTER INSERT" and instead of modifying and inserting the data I was allowing my command to insert the data to the table and then updated it using the trigger.