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?

Answer 1

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.

Answer 2

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

For example, this code would throw an exception:

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

Answer 3

I had this error when fixing the new warning for nonnullable reference not initialized.

CS8618 - Non-nullable variable must contain a non-null value when exiting constructor. Consider declaring it as nullable.

https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/compiler-messages/nullable-warnings?f1url=%3FappId%3Droslyn%26k%3Dk(CS8618)#nonnullable-reference-not-initialized

Example class with warning looked like this:

public class BaseContainer
{
    public int Id { get; set; }

    public List<ContainerChild> ContainerChildren { get; set; } = new List<ContainerChild>();
}

public class ContainerChild
{
    public int Id { get; set; }

    public int BaseContainerId { get; set; }

    public BaseContainer BaseContainer { get; set; }
}

I then resolved the warning like this in ContainerChild:

public BaseContainer BaseContainer { get; set; } = new BaseContainer();

However when I then added a ContainerChild to BaseContainer I created a new BaseContainer instead.

var baseContainer = _dbContext.BaseContainer.SingleOrDefault(x => x.Id == id);
var containerChild = new ContainerChild();
baseContainer.ContainerChildren.Add(containerChild);

Fixed by setting BaseContainer member to nullable reference type in ContainerChild like this:

public BaseContainer? BaseContainer { get; set; }

Answer 4

A non-query based solution: In my case, double click on a button in my .Net Core application caused the cuncurrency, so I've added a code to click method of the button to make it idle for 100 ms. As I used the DevExpress, I've added the following code:

    function ButtonClick(e) {  
        setTimeout(function () {  
            e.SetEnabled(false);  
        }, 100);  
// the rest of the code
    } 

You may find other solutions for prevent the double click everywhere else.

Answer 5

In my case, the error was that I was generating entity IDs / primary keys in the code and Entity Framework wanted to additionally generate values ​​on the database side. It helped to specify the values ​​generated when creating the entity, and more specifically the lack of their generation.

Adnotation:

[DatabaseGenerated(DatabaseGeneratedOption.None)]
public Guid Id { get; set; }

Fluent API:

e.HasKey(e => e.Id);
e.Property(e => e.Id).ValueGeneratedNever();

Answer 6

Sometimes Timestamp(Sql Server) fields may cause the problem. Remove them from model class may fix the issue if they are not necessary.

Answer 7

For SQL Server, EF Core included a timestamp property in the Where clause, which I still don't get why. So I had to keep track of the primary key and the timestamp to re-attach the entity.

TL;DR: Check your (EF Core) generated SQL if there is something missing in the Where clause of your Update statement.

Example:

Entity:

CREATE TABLE [dbo].[Blog]
    [BlogID] [int] IDENTITY(0,1) NOT NULL,
    [sysTS] [timestamp] NOT NULL,
    [IsPublished] [bit] NOT NULL

public class Blog {
  public int BlogId { get; set; }   
  public byte[] SysTS { get; set; }
  public bool IsPublished { get; set; }
}

Code:

var blog = new Blog { IsPublished = false };
using(var context = CreateNewContext()) {
  context.Blog.Add(blog);
  await context.SaveChangesAsync();
}
var blogId = blog.BlogID
// var blogSysTs = blog.SysTs; // this I had to track too

// ... do other stuff

// here also SysTS = blogSysTs was needed
var blog2 = new Blog { BlogID = blogId, IsPublished = true }; 
using(var context2 = CreateNewContext()) {
  var entry = context2.Blog.Attach(blog2);
  entry.Property(b => b.IsPublished).IsModified = true;
  await context2.SaveChangesAsync();
}

Which resultet in something like:

UPDATE Blog
SET IsPublished = @p1
WHERE BlogID = @p2 and SysTS IS NULL

Only with the SysTS tracked there was a legit update query:

UPDATE Blog
SET IsPublished = @p1
WHERE BlogID = @p2 and SysTS = @p3

I get that this certainly has to do with out SQL Server set up, I just can't figure out why. But the gist of it: Check your EF Core generated SQL queries to see, why the row wasn't updated.

Answer 8

I was getting this error in delete operation.

In my case, I was trying to delete a row and the rows related (foreign key relation) to it in other tables. It turned out an trigger was added in the DB to delete related rows if the parent is deleted. In case of deletion, make sure the rows you are trying to remove is exists or if you have any triggers added to delete rows related.

Answer 9

In my case, the problem was that I tried to remove a value from a table without checking if that value actually existed:

public DbSet<AdminSettings> AdminSettingsTable { get; set; }

public AdminSettings AdminSettings
  {
    get
    {
      return AdminSettingsTable.FirstOrDefault();
    }
    set
    {
      // This if-statement prevents the crash
      if (AdminSettingsTable.Contains(value))
      {
        AdminSettingsTable.Remove(value);
      }
      SaveChanges();
      AdminSettingsTable.Add(value);
   }
}

Answer 10

None of the accepted answers worked for me using Razor Pages. I ended up using an InputModel Input to fix it. (I have my own class for the roles).

    public class AppRole:IdentityRole<int> { }

 [BindProperty]
 public InputModel Input { get; set; }
 public class InputModel
 {
     [Required]
     public string Name { get; set; }

     public string NormalizedName=>Name.ToUpper();

     public int Id { get; set; }

     // Used to check for concurrency in the post method
     public string OriginalConcurrencyStamp { get; set; }

 }

Seeding the Input from the OnGet method.

 public IActionResult OnGet(int? id)
 {
      if (id == null)
      {
          return NotFound();
      }

      var appRole = _context.Roles.Find(id);

      if(appRole == null) { return NotFound(); }

      Input.Id = id.Value;
      Input.Name = appRole.Name;
      Input.OriginalConcurrencyStamp = appRole.ConcurrencyStamp;

      return Page();
  }

Then on the post method I 'Find' the role and update it there.

 public IActionResult OnPostAsync()
 {
     if (!ModelState.IsValid)
     {
         return Page();
     }

     var appRole = _context.Roles.Find(Input.Id);

     // The role has been deleted
     if(appRole == null)
     {
         ViewData["Error"] = "This Role has been deleted by someone else.\nReturn to the list.";
         return Page();
     }

     // the role has been changed by another user.
     if(appRole.ConcurrencyStamp != Input.OriginalConcurrencyStamp)
     {
         ViewData["Error"] = "This Role has been changed by someone else.\nReturn to the list.";
         return Page();
     }

     // no need to SaveChanges if there are no changes.
     var hasChanges = false;
     if(appRole.Name != Input.Name)
     {
         appRole.Name = Input.Name;
         hasChanges = true;
     }

     if(appRole.SortOrder != Input.SortOrder)
     {
         appRole.SortOrder = Input.SortOrder;
         hasChanges = true;
     }

     if(appRole.NormalizedName != Input.NormalizedName)
     {
         appRole.NormalizedName = Input.NormalizedName;
         hasChanges = true;
     }

     if(!hasChanges)
     {
         ViewData["Error"] = "No Changes Detected.";
         return Page();
     }

     appRole.ConcurrencyStamp = Guid.NewGuid().ToString();

     _context.Entry(appRole).State = EntityState.Modified;

     try
     {
         _context.SaveChanges();
     }
     catch (DbUpdateConcurrencyException ex)
     {
         if (!AppRoleExists(Input.Id))
         {
             return NotFound();
         }
         else { throw; }
     }

     return RedirectToPage("./Index");
 }

And my Edit.cshtml file looks like this.

@page
@model MyApp.Areas.Identity.Pages.Account.Manage.Roles.EditModel

@{
    ViewData["Title"] = "Edit";
    Layout = "~/Pages/Shared/_Layout.cshtml";
}

<h1>Edit</h1>

<h4>Role</h4>
<hr />
<div class="row">
    <div class="col-md-4">
        <form method="post">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <input type="hidden" asp-for="Input.Id" />
            <input type="hidden" asp-for="Input.OriginalConcurrencyStamp" />
            <label class="text-danger">@ViewData["Error"]</label>
            <div class="form-group">
                <label asp-for="Input.Name" class="control-label"></label>
                <input asp-for="Input.Name" class="form-control" />
                <span asp-validation-for="Input.Name" class="text-danger"></span>
            </div>
            <div class="form-group">
                <input type="submit" value="Save" class="btn btn-primary" />

            </div>
        </form>
    </div>
</div>

<div>
    <a asp-page="./Index">Back to List</a>
</div>

@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

Answer 11

in mysql ,it needs a AUTO_INCREMENT ID

like this:

ALTER TABLE wfdbcore201test.wftransitioninstance MODIFY COLUMN ID int(11) NOT NULL AUTO_INCREMENT FIRST;

Answer 12

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.

Answer 13

Same error here, what ended up being the issue is I'm inserting while reusing the code for updating ...

  Role role = new Role();
  role.Value = input;
  context.Add(role);                    
  await context.SaveChangesAsync();

There's no state to be changed when inserting ...

Answer 14

If you have a Trigger INSTEAD OF INSERT on the table, the Database cancel the operation and EntityFramework fire this error.

Answer 15

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.

Answer 16

I have solved this by combining the two methods

var thisRole = _db.Roles.Where(r => r.Id.Equals(role.Id, 
StringComparison.CurrentCultureIgnoreCase)).FirstOrDefault();
_db.Roles.Attach(thisRole);
thisRole.Name = role.Name;
_db.SaveChanges();

Answer 17

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();