Solution For: Store Update, Insert, or Delete Statement Affected an Unexpected Number of Rows (0)

Entity Framework: Store update, insert, or delete statement affected an unexpected number of rows (0).

That's a side-effect of a feature called optimistic concurrency.

Not 100% sure how to turn it on/off in Entity Framework but basically what it's telling you is that between when you grabbed the data out of the database and when you saved your changes someone else has changed the data (Which meant when you went to save it 0 rows actually got updated). In SQL terms, their update query's where clause contains the original value of every field in the row, and if 0 rows are affected it knows something's gone wrong.

The idea behind it is that you won't end up overwriting a change that your application didn't know has happened - it's basically a little safety measure thrown in by .NET on all your updates.

If it's consistent, odds are it's happening within your own logic (EG: You're actually updating the data yourself in another method in-between the select and the update), but it could be simply a race condition between two applications.

EF:Store update, insert, or delete statement affected an unexpected number of rows (0)

It looks like that I have get some ideas about this error, expect the most common solutions, we also need to consider the main table association problem of the updated data table. Before the table status is maked as modified, the main table has been maked as modified once, as a result, the table status is modified twice and the rowversion value is disaccord with the value in the database.

Solution for: Store update, insert, or delete statement affected an unexpected number of rows (0)

Solution:

try {
    context.SaveChanges();
} catch (OptimisticConcurrencyException) {
    context.Refresh(RefreshMode.ClientWins, db.Articles);
    context.SaveChanges();
}

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted

On your Controller Action, the parameter should be the List. You need to iterate and update.

    [HttpPost]
    public ActionResult Edit(List<RESULT> results)
    {
       if (ModelState.IsValid)
        {
            foreach(var item in results)
            {
                var model =_context.Find(item.ID);
                if(model != null) {  
                   model.TestID = item.TestID;
                   model.TestName = item.TestName;
                   //update columns you want to update
                   _context.SaveChanges();
                }
            }
            ViewBag.Message = "Updated Successfully";
            return RedirectToAction("Details");
        }
        return View(results);
    }

View

@if(ViewBag.Message != null){
   @ViewBag.Message
}

Store update, insert, or delete statement affected an unexpected number of rows (0)

Try adding this line of SQL code inside your trigger AFTER the insert:

select g_id from [groupLocation] where @@ROWCOUNT > 0 and g_id = scope_identity();

I assume g_id is your identity column, if not replace both occurences of g_id in the statement above with the identity column.

Also read this SO question - Error when inserting into table having instead of trigger from entity data framework

insert statement affected an unexpected number of rows (0)

Turn out that EF produces this error when you have SQL triggers on your database table. Adding following at the end of the trigger solved the problem. However this may not be the perfect solution.

SELECT * FROM deleted UNION ALL
SELECT * FROM inserted;

Edit Id - Store update, insert, or delete statement affected an unexpected number of rows (0)

When using ORMs like Entity Framework, the UPDATE command is composed basically like this:

UPDATE table 
SET ChangedCol = ChangedVal 
WHERE PrimaryKeyCol = PrimaryKeyValAtLoadTime

Afterwards it checks the number of affected rows to match the expected value.

So when you try to change the PK the update command will update no row and the check will fail.

The real question is: Why do you want to change the primary key value?

---

Related Topics