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

Question

I am using Entity Framework to populate a grid control. Sometimes when I make updates I get the following error:

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

I can't figure out how to reproduce this. But it might have something to do with how close together I make the updates. Has anyone seen this or does anyone know what the error message refers to?

Edit: Unfortunately I am no longer at liberty to reproduce the problem I was having here, because I stepped away from this project and don't remember if I eventually found a solution, if another developer fixed it, or if I worked around it. Therefore I cannot accept any answers.

Answer 1

I ran into this and it was caused by the entity's ID (key) field not being set. Thus when the context went to save the data, it could not find an ID = 0. Be sure to place a break point in your update statement and verify that the entity's ID has been set.

From Paul Bellora's comment

I had this exact issue, caused by forgetting to include the hidden ID input in the .cshtml edit page

Answer 2

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.

Answer 3

Wow, lots of answers, but I got this error when I did something slightly different that no on else has mentioned.

Long story short, if you create a new object and tell EF that its modified using the EntityState.Modified then it will throw this error as it doesn't yet exist in the database. Here is my code:

MyObject foo = new MyObject()
{
    someAttribute = someValue
};

context.Entry(foo).State = EntityState.Modified;
context.SaveChanges();

Yes, this seems daft, but it arose because the method in question used to have foo passed to it having been created earlier on, now it only has someValue passed to it and creates foo itself.

Easy fix, just change EntityState.Modified to EntityState.Added or change that whole line to:

context.MyObject.Add(foo);

Answer 4

I was facing this same scaring error... :) Then I realized that I was forgetting to set a

@Html.HiddenFor(model => model.UserProfile.UserId)

for the primary key of the object being updated! I tend to forget this simple, but very important thingy!

By the way: HiddenFor is for ASP.NET MVC.

Answer 5

Check whether you forgot the "DataKeyNames" attribute in the GridView. it's a must when modifying data within the GridView

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.datakeynames.aspx

Answer 6

The issue is caused by either one of two things :-

1. You tried to update a row with one or more properties are Concurrency Mode: Fixed .. and the Optimistic Concurrency prevented the data from being saved. Ie. some changed the row data between the time you received the server data and when you saved your server data.
2. You tried to update or delete a row but the row doesn't exist. Another example of someone changing the data (in this case, removing) in between a retrieve then save OR you're flat our trying to update a field which is not an Identity (ie. StoreGeneratedPattern = Computed) and that row doesn't exist.

Answer 7

I got this same error because part of the PK was a datetime column, and the record being inserted used DateTime.Now as the value for that column. Entity framework would insert the value with millisecond precision, and then look for the value it just inserted also with millisecond precision. However SqlServer had rounded the value to second precision, and thus entity framework was unable to find the millisecond precision value.

The solution was to truncate the milliseconds from DateTime.Now before inserting.

Answer 8

I had the same problem, I figure out that was caused by the RowVersion which was null. Check that your Id and your RowVersion are not null.

for more information refer to this tutorial

http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application

Answer 9

I was having same problem and @webtrifusion's answer helped find the solution.

My model was using the Bind(Exclude) attribute on the entity's ID which was causing the value of the entity's ID to be zero on HttpPost.

namespace OrderUp.Models
{
[Bind(Exclude = "OrderID")]
public class Order
{
    [ScaffoldColumn(false)]
    public int OrderID { get; set; }

    [ScaffoldColumn(false)]
    public System.DateTime OrderDate { get; set; }

    [Required(ErrorMessage = "Name is required")]
    public string Username { get; set; }
    }
}   

Answer 10

I also came across this error. The problem it turned out was caused by a Trigger on the table I was trying to save to. The Trigger used 'INSTEAD OF INSERT' which means 0 rows ever got inserted to that table, hence the error. Luckily in may case the trigger functionality was incorrect, but I guess it could be a valid operation that should somehow be handled in code. Hope this helps somebody one day.

Answer 11

While editing include the id or primary key of the entity as a hidden field in the view

ie

      @Html.HiddenFor(m => m.Id)

that solves the problem.

Also if your model includes non-used item include that too and post that to the controller

Answer 12

I started getting this error after changing from model-first to code-first. I have multiple threads updating a database where some might update the same row. I don't know why I didn't have a problem using model-first, assume that it uses a different concurrency default.

To handle it in one place knowing the conditions under which it might occur, I added the following overload to my DbContext class:

using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;

public class MyDbContext: DbContext {
...
        public int SaveChanges(bool refreshOnConcurrencyException, RefreshMode refreshMode = RefreshMode.ClientWins) {
            try {
                return SaveChanges();
            }
            catch (DbUpdateConcurrencyException ex) {
                foreach (DbEntityEntry entry in ex.Entries) {
                    if (refreshMode == RefreshMode.ClientWins)
                        entry.OriginalValues.SetValues(entry.GetDatabaseValues());
                    else
                        entry.Reload();
                }
                return SaveChanges();
            }
        }
}

Then called SaveChanges(true) wherever applicable.

Answer 13

The line [DatabaseGenerated(System.ComponentModel.DataAnnotations.DatabaseGeneratedOption.None)] did the trick in my case:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;


[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int? SomeNumber { get; set; }

Answer 14

You need to explicitly include a BoundField of the primary key. If you don't want the user to see the primary key, you have to hide it via css:

    <asp:BoundField DataField="Id_primary_key" ItemStyle-CssClass="hidden" 
HeaderStyle-CssClass="hidden" />

Where 'hidden' is a class in css that has it's display set to 'none'.

Answer 15

I came across this issue on a table that was missing a primary key and had a DATETIME(2, 3) column (so the entity's "primary key" was a combination of all the columns)... When performing the insert the timestamp had a more precise time (2018-03-20 08:29:51.8319154) that was truncated to (2018-03-20 08:29:51.832) so the lookup on key fields fails.

Answer 16

Just make sure table and form both have primary key and edmx updated.

i found that any errors during update were usually because of: - No primary key in Table - No primary key in Edit view/form (e.g. @Html.HiddenFor(m=>m.Id)

Answer 17

I also had this error. There are some situations where the Entity may not be aware of the actual Database Context you are using or the Model may be different. For this, set: EntityState.Modified; to EntityState.Added;

To do this:

if (ModelState.IsValid)
{
context.Entry(yourModelReference).State = EntityState.Added;
context.SaveChanges();
}

This will ensure the Entity knows youre using or adding the State youre working with. At this point all the correct Model Values need to be set. Careful not to loose any changes that may have been made in the background.

Hope this helps.

Answer 18

  @Html.HiddenFor(model => model.RowVersion)

My rowversion was null, so had to add this to the view which solved my issue

Answer 19

    public void Save(object entity)
    {
        using (var transaction = Connection.BeginTransaction())
        {
        try
                {
                    SaveChanges();
                    transaction.Commit();
                }
                catch (OptimisticConcurrencyException)
                {
                    if (ObjectStateManager.GetObjectStateEntry(entity).State == EntityState.Deleted || ObjectStateManager.GetObjectStateEntry(entity).State == EntityState.Modified)
                        this.Refresh(RefreshMode.StoreWins, entity);
                    else if (ObjectStateManager.GetObjectStateEntry(entity).State == EntityState.Added)
                        Detach(entity);
                    AcceptAllChanges(); 
                    transaction.Commit();
                }
        }
    }

Answer 20

I had the same problem. In my case I was trying to update the primary key, which is not allowed.

Answer 21

I got this error sporadically when using an async method. Has not happened since I switched to a synchronous method.

Errors sporadically:

[Authorize(Roles = "Admin")]
[HttpDelete]
[Route("file/{id}/{customerId}/")]
public async Task<IHttpActionResult> Delete(int id, int customerId)
{
    var file = new Models.File() { Id = id, CustomerId = customerId };
    db.Files.Attach(file);
    db.Files.Remove(file);

    await db.SaveChangesAsync();

    return Ok();
}

Works all the time:

[Authorize(Roles = "Admin")]
[HttpDelete]
[Route("file/{id}/{customerId}/")]
public IHttpActionResult Delete(int id, int customerId)
{
    var file = new Models.File() { Id = id, CustomerId = customerId };
    db.Files.Attach(file);
    db.Files.Remove(file);

    db.SaveChanges();

    return Ok();
}

Answer 22

I got that error when I was deleting some rows in the DB (in the loop), and the adding the new ones in the same table.

The solutions for me was, to dynamicaly create a new context in each loop iteration

Answer 23

This will also happen if you are trying to insert into a unique constraint situation, ie if you can only have one type of address per employer and you try to insert a second of that same type with the same employer, you will get the same problem.

OR

This could also happen if all of the object properties that were assigned to, they were assigned with the same values as they had before.

        using(var db = new MyContext())
        {
            var address = db.Addresses.FirstOrDefault(x => x.Id == Id);

            address.StreetAddress = StreetAddress; // if you are assigning   
            address.City = City;                   // all of the same values
            address.State = State;                 // as they are
            address.ZipCode = ZipCode;             // in the database    

            db.SaveChanges();           // Then this will throw that exception
        }

Answer 24

I got this exception when attaching an object that didn't exist in the database. I had assumed the object was loaded from a separate context, but if it was the user's first time visiting the site, the object was created from scratch. We have auto-incrementing primary keys, so I could replace

context.Users.Attach(orderer);

with

if (orderer.Id > 0) {
    context.Users.Attach(orderer);
}

Answer 25

One way to debug this problem in an Sql Server environment is to use the Sql Profiler included with your copy of SqlServer, or if using the Express version get a copy of Express Profiler for free off from CodePlex by the following the link below:

Express Profiler

By using Sql Profiler you can get access to whatever is being sent by EF to the DB. In my case this amounted to:

exec sp_executesql N'UPDATE [dbo].[Category]
SET [ParentID] = @0, [1048] = NULL, [1033] = @1, [MemberID] = @2, [AddedOn] = @3
WHERE ([CategoryID] = @4)
',N'@0 uniqueidentifier,@1 nvarchar(50),@2 uniqueidentifier,@3 datetime2(7),@4 uniqueidentifier',
@0='E060F2CA-433A-46A7-86BD-80CD165F5023',@1=N'I-Like-Noodles-Do-You',@2='EEDF2C83-2123-4B1C-BF8D-BE2D2FA26D09',
@3='2014-01-29 15:30:27.0435565',@4='3410FD1E-1C76-4D71-B08E-73849838F778'
go

I copy pasted this into a query window in Sql Server and executed it. Sure enough, although it ran, 0 records were affected by this query hence the error being returned by EF.

In my case the problem was caused by the CategoryID.

There was no CategoryID identified by the ID EF sent to the database hence 0 records being affected.

This was not EF's fault though but rather a buggy null coalescing "??" statement up in a View Controller that was sending nonsense down to data tier.

Answer 26

Well i have this same issue. But this was due to my own mistake. Actually i was saving an object instead of adding it. So this was the conflict.

Answer 27

None of the above answers quite covered my situation and the solution to it.

Code where the error was thrown in MVC5 controller:

        if (ModelState.IsValid)
        {
            db.Entry(object).State = EntityState.Modified; 
            db.SaveChanges(); // line that threw exception
            return RedirectToAction("Index");
        }

I received this exception when I was saving an object off an Edit view. The reason it threw it was because when I went back to save it, I had modified the properties that formed the primary key on the object. Thus, setting its state to Modified didn't make any sense to EF - it was a new entry, not a previously saved one.

You can solve this by either A) modifying the save call to Add the object, or B) just don't change the primary key on edit. I did B).

Answer 28

When the accepted answer said "it won't end up overwriting a change that your application didn't know has happened", I was skeptic because my object was newly created. But then it turns out, there was an INSTEAD OF UPDATE, INSERT- TRIGGER attached to the table which was updating a calculated column of the same table.

Once I change this to AFTER INSERT, UPDATE, it was working fine.

Answer 29

This happened to me due to a mismatch between datetime and datetime2. Strangely, it worked fine prior to a tester discovering the issue. My Code First model included a DateTime as part of the primary key:

[Key, Column(Order = 2)]  
public DateTime PurchasedDate { get; set; } = (DateTime)SqlDateTime.MinValue;

The generated column is a datetime column. When calling SaveChanges, EF generated the following SQL:

-- Region Parameters
DECLARE @0 Int = 2
DECLARE @1 Int = 25
DECLARE @2 Int = 141051
DECLARE @3 DateTime2 = '2017-07-27 15:16:09.2630000' --(will not equal a datetime value)
-- EndRegion
UPDATE [dbo].[OrganizationSurvey]
SET [OrganizationSurveyStatusId] = @0
WHERE ((([SurveyID] = @1) AND ([OrganizationID] = @2)) AND ([PurchasedDate] = @3))

Because it was trying to match a datetime column with a datetime2 value, it returned no results. The only solution I could think of was to change the column to a datetime2:

[Key, Column(Order = 2, TypeName = "DateTime2")]  
public DateTime PurchasedDate { get; set; } = (DateTime)SqlDateTime.MinValue;

Answer 30

I got this issue when I accidently tried to update the object instead of save!

I had

 if (IsNewSchema(model))
            unitOfWork.SchemaRepository.Update(schema);
        else
            unitOfWork.SchemaRepository.Insert(schema);

when I should of had

 if (IsNewSchema(model))
            unitOfWork.SchemaRepository.Insert(schema);
        else
            unitOfWork.SchemaRepository.Update(schema);

Answer 31

This may happen if trying to update a record with an Id that does not exist in the database.

Answer 32

Just had the same problem.

I'm using EF 6, Code First + Migrations. The problem was that our DBA created a constraint on the table that threw the error.

Answer 33

For those of you using AutoMapper If you are updating an entity that has foreign keys to another entity(or entities), make sure all of the foreign entities have their primary key set to database generated (or auto-incremented for MySQL).

For example:

public class BuyerEntity
{
    [Key]
    public int BuyerId{ get; set; }

    public int Cash { get; set; }

    public List<VehicleEntity> Vehicles { get; set; }

    public List<PropertyEntity> Properties { get; set; }

}

Vehicles and Properties are stored in other tables than Buyers. When you add a new buyer, AutoMapper and EF will automatically update the Vehicles and Properties tables so if you don't have auto-increment set up on either of those tables (like I didn't), then you will see the error from the OP's question.

Answer 34

I got this exception and i set id column as auto increment in my database's table and then it working fine

Answer 35

If you are trying to create mapping in your edmx file to a "function Imports", this can result this error. Just clear the fields for insert, update and delete that is located in Mapping Details for a given entity in your edmx, and it should work. I hope I made it clear.

Answer 36

I ran into this using Telerik's RadGrid. I had the primary key as a gridbound column that was set to read only. It would work fine if the column was display="false" but readonly="true" caused the problem. I solved it by having the gridbound column display=false and adding a separate template column for display

<telerik:GridBoundColumn HeaderText="Shouldnt see" Display="false" 
     UniqueName="Id" DataField="Id">
</telerik:GridBoundColumn>
<telerik:GridTemplateColumn HeaderText="Id" UniqueName="IdDisplay">
    <ItemTemplate>
        <asp:Label ID="IDLabel" runat="server" 
            Text='<%# Eval("Id") %>'></asp:Label>                               
    </ItemTemplate>
</telerik:GridTemplateColumn> 

Answer 37

In our case this error was caused by marking entities as modified when none of their properties 'really' changed. For example when you assign same value to a property, context may see that as an update where database doesn't.

Basically we ran a script to repopulate one property with concatenated values from other properties. For a lot of records that meant no change, but it flagged them as modified. DB returned different number of updated objects which presumably triggered that exception.

We solved it by checking the property value and only assigning new one if different.

Answer 38

Got this error when using SaveChanges(false) and then later SaveChanges() on the same context, in a unitofwork where multiple rows were being deleted from two tables (in the context)(SaveChanges(False) was in one of the deletes. Then in the calling function SaveChanges() was being called.... The solution was to remove the unnecessary SaveChanges(false).

Answer 39

I'll throw this in just in case someone is running into this issue while working in a parallel loop:

Parallel.ForEach(query, deet =>
{
    MyContext ctx = new MyContext();
    //do some stuff with this to identify something
    if(something)
    {
         //Do stuff
         ctx.MyObjects.Add(myObject);
         ctx.SaveChanges() //this is where my error was being thrown
    }
    else
    {
        //same stuff, just an update rather than add
    }
}

I changed it to the following:

Parallel.ForEach(query, deet =>
{
    MyContext ctxCheck = new MyContext();
    //do some stuff with this to identify something
    if(something)
    {
         MyContext ctxAdd = new MyContext();
         //Do stuff
         ctxAdd .MyObjects.Add(myObject);
         ctxAdd .SaveChanges() //this is where my error was being thrown
    }
    else
    {
        MyContext ctxUpdate = new MyContext();
        //same stuff, just an update rather than add
        ctxUpdate.SaveChanges();
    }
}

Not sure if this is 'Best Practice', but it fixed my issue by having each parallel operation use its own context.

Answer 40

I had a similar problem today, I'll document it here, as it's not quite the optimistic concurrency error.

I'm converting an old system to a new database and it has couple of thousand entities that I've had to script over to the new system. However, to aid with sanity I've opted to keep the original unique IDs and so was injecting that into the new object and then trying to save it.

The problem I had is that I'd used MVC Scaffolding to create base repositories and they have a pattern in their UpdateOrInsert method, that basically checks to see if the Key attribute is set before it either adds the new entity or changes its state to modified.

Because the Guid was set, it was trying to modify a row that didn't actually exist in the database.

I hope this helps someone else!

Answer 41

Recently, I'm trying upgrade EF5 to EF6 sample project . The table of sample project has decimal(5,2) type columns. Database migration successfully completed. But, initial data seed generated exception.

Model :

    public partial class Weather
    {
    ...
    public decimal TMax {get;set;}
    public decimal TMin {get;set;}
    ...
    }

Wrong Configuration :

public partial class WeatherMap : EntityTypeConfiguration<Weather>
{

    public WeatherMap()
    {
        ...
        this.Property(t => t.TMax).HasColumnName("TMax");
        this.Property(t => t.TMin).HasColumnName("TMin");
        ...
    }
}

Data :

    internal static Weather[] data = new Weather[365]
    {
      new Weather() {...,TMax = 3.30M,TMin = -12.00M,...},
      new Weather() {...,TMax = 5.20M,TMin = -10.00M,...},
      new Weather() {...,TMax = 3.20M,TMin = -8.00M,...},
      new Weather() {...,TMax = 11.00M,TMin = -7.00M,...},
      new Weather() {...,TMax = 9.00M,TMin = 0.00M,...},
    };

I found the problem, Seeding data has precision values, but configuration does not have precision and scale parameters. TMax and TMin fields defined with decimal(10,0) in sample table.

Correct Configuration :

public partial class WeatherMap : EntityTypeConfiguration<Weather>
{

    public WeatherMap()
    {
        ...
        this.Property(t => t.TMax).HasPrecision(5,2).HasColumnName("TMax");
        this.Property(t => t.TMin).HasPrecision(5,2).HasColumnName("TMin");
        ...
    }
}

My sample project run with: MySql 5.6.14, Devart.Data.MySql, MVC4, .Net 4.5.1, EF6.01

Best regards.

Answer 42

Got the same problem when removing item from table(ParentTable) that was referenced by another 2 tables foreign keys with ON DELETE CASCADE rule(RefTable1, RefTable2). The problem appears because of "AFTER DELETE" trigger at one of referencing tables(RefTable1). This trigger was removing related record from ParentTable as result RefTable2 record was removed too. It appears that Entity Framework, while in-code was explicitly set to remove ParentTable record, was removing related record from RefTable1 and then record from RefTable2 after latter operation this exception was thrown because trigger already removed record from ParentTable which as results removed RefTable2 record.

Answer 43

we forgot mention "enctype" posting "multi-part" form data. One of my another scenario that I've just now face...

Answer 44

I had this issue with Mvc identity when registering new user instead of:

var result = await UserManager.CreateAsync(user);

I was doing:

var result = await UserManager.UpdateAsync(user);

Answer 45

This just happened to me. I was running Aurora (AWS MySQL) and tried to add records to a table. The field marked [Key] in the model was mapped to an auto-incremented field in the table... or so I thought. It was set as Primary Key, but it was not set to auto-increment. So setting it to auto-increment fixed my issue.