Unable to edit db entries using EFCore, EntityState.Modified: "Database operation expected to affect 1 row(s) but actually affected 0 row(s)."

Question

I'm using Identity Core 1.0 with ASP.NET MVC Core 1.0 and Entity Framework Core 1.0 to create a simple user registration system with this article as a starting point, and I am trying to add user roles. I can add user roles, but I'm unable to edit them. Here is the Edit action in the RolesController:

    [HttpPost]
    [ValidateAntiForgeryToken]
    public IActionResult Edit(IdentityRole role)
    {
        try
        {
            _db.Roles.Attach(role);
            _db.Entry(role).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
            _db.SaveChanges();
            return RedirectToAction("Index");
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
            return View();
        }
    }

Here is the form in the corresponding view:

@model Microsoft.AspNet.Identity.EntityFramework.IdentityRole
@{
    ViewBag.Title = "Edit";
}

<h2>Edit Role</h2>
<hr />
@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    @Html.ValidationSummary(true)
    @Html.HiddenFor(model => model.Id)
    <div>Role name</div>
    <p>@Html.TextBoxFor(model => model.Name)</p>
    <input type="submit" value="Save" />
}

The new role name does not save to the database, and I get the following exception: 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 was able to use this exact code (with the Microsoft.AspNet.Identity.EntityFramework dependency instead of EntityFrameworkCore) to edit database entries using EF 7, Identity 3, etc.

Any thoughts on why this code will not allow database entries to be modified?

Solution 1

Unless there is a hidden exception that is hiding behind this as a dumb random exception, the reason is clearly stated in the exception.

Check the Id on the role object as you receive it on your Edit action and try to lookup that id in the database. The exception message you see states that, it is expecting to find a row with a matching Id of the object you attached, but it is not, so it is failing to do the update, since it could not locate a matching row to update it.

EDIT :

You are attaching the entity twice, remove the call to .Attach(role) and keep the line below it which is sufficient to add the object to the tracking context in a modified state.

//_db.Roles.Attach(role); //REMOVE THIS LINE !.
_db.Entry(role).State = Microsoft.EntityFrameworkCore.EntityState.Modified;

Beware that setting the state of the entry to modified will update all the property values upon calling .SaveChanges(), so in case you want to update only certain properties refer to this answer.

If this doesn't solve your problem, please check for any inner exceptions that you might've missed. Sometimes the exception messages don't make sense and mask the real problem which you might be able to find in the inner exception.

Solution 2

You can try as shown below.

[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Edit(IdentityRole role)
{
    try
    {
         _db.Entry(role).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
        _db.SaveChanges();
        return RedirectToAction("Index");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex);
        return View();
    }
}

Note :

When _db.Entry(role).State = EntityState.Modified;

• you are not only attaching the entity to the _db, you are also marking the whole entity as dirty.
• When you do _db.SaveChanges(), EF will generate an update statement that will update all the fields of the entity.

When _db.Roles.Attach(role)

• attaches the entity to the context without marking it dirty.
• It is like _db.Entry(role).State = EntityState.Unchanged;.
• unless you then proceed to update a property on the entity, the next time you call context.SaveChanges(), EF will not generate a database update for this entity.

i.e. If you need to generate a database update,you have to do like this :

_db.Roles.Attach(role); // State = Unchanged
role.RoleName = "Admin"; // State = Modified, and only the RoleName property is dirty
context.SaveChanges();

Solution 3

This could also happen if you try to update an entity which has new related entities.

someEntity.AnotherEntity = new AnotherEntity();
dbContext.Update(someEntity);
dbContext.SaveChanges(); // Exception

Instead, do this:

someEntity.AnotherEntity = new AnotherEntity();
dbContext.AnotherEntitySet.Add(someEntity.AnotherEntity);
dbContext.Update(someEntity);
dbContext.SaveChanges(); // No Exception

Solution 4

After executing the Update or Delete, EF Core reads the number of rows that were affected.

SELECT [ExampleEntityId]
FROM [ExampleEntities]
WHERE @@ROWCOUNT = 1 AND
[ExampleEntityId] = scope_identity();

If your entities do not have IdentityColumn thats primary key, EF Core throws DbUpdateConcurrencyException.

In my case I added primary keys(and set them as identity) to related tables in database.

Solution 5

Your answers doesn't work for me. And I solved my error like this. Changed Model class proporties

[Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public string Id { get; set; }

after ı changed My mapping class

builder.Property(c => c.Id).HasColumnName("ID").IsRequired();

Last change is

 CustomerEntity thisrole = (from x in db.Customers
                          where x.Id == Id
                          select x).First();
            thisrole.Id = accountNum;
            thisrole.Name = name;
            thisrole.Phone = phone;
            thisrole.Email = email;
            thisrole.Address = address;
            db.SaveChanges();              
            return true;

I hope This solution works for someone.

View more solutions

Comments

[jmk22]

The ID of the role object being passed to the Edit action is the same as the ID of the role in the database. Is there an issue because the name of the entry is changing, so EF can't find the right entry in the database?

[jmk22]

I see. My thought here was that I only want to edit the name, and perhaps the name change was not letting EF find the correct object to edit. Either way, I've tried it both with and without the Attach line, and I still run into the same error.

[Siraj Mansour]

@jmk22 hmm i see that i missed something. Remove the .Attach() call, check my edit

[jmk22]

I removed the line, but get the same 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.), and the inner exception is null. It looks like EF might be looking for a role in the database with the edited name, so it's not returning anything.

[julian zapata]

I'm having this issue. I have a trigger INSTEAD OF INSERT on a table and doesn't work but I have another trigger in the same way in other table and it works. I don't know what is happening.

[Jason Landbridge]

Thanks, this fixed it for me! I only wanted to update the parent entity so I nulled all related navigation properties before the update and this fixed it!

[Gert Arnold]

This isn't much of an answer if you don't know what's going on. It's not clear how your class model / database model were created and if you work code-first or database first. It's not as if EF core starts using a timestamp property out of the blue.

[Radall]

@GertArnold The answer lies in the TL;DR to check whether EF expects other columns in the Where clause which in my case explained why it couldn't affect the expected row. Edit: The tale about the timestamp was just an example.

[Gert Arnold]

Yeah, that reduces the answer to something that's good for a comment. The question isn't clear and shouldn't have been answered by anybody. All the "answers" here are no answers. They basically say: I had the same error. I wish everybody would only have asked more info before answering. "Look at the SQL query" could have been one of those comments.

[Radall]

Sorry you feel that way. But looking at the stats for this querstion I'd say it helped some people and I wanted to contribute since I came across this facing the same problem.

[Radall]

But I guess you're also right, it could have been simply a comment with the TL;DR

[Александър К.]

Working! In my case I changed my P-Keys from int to GUID and added Id = Guid.NewGuid(). So this trigger this error. I removed manual generating of Id and now EfCore automatically generates Id as GUID. Im using EfCore.