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

User · Answer

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 lt VehicleEntity gt  Vehicles   get  set         public List lt PropertyEntity gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Html HiddenFor model   gt  model RowVersion    My rowversion was null  so had to add this to the view which solved my issue

User · Answer

While editing include the id or primary key of the entity as a hidden field in the view  ie         Html HiddenFor m   gt  m Id    that solves the problem   Also if your model includes non-used item include that too and post that to the controller

User · Answer

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

User · Answer

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

User · Answer

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   gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The issue is caused by either one of two things  -   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   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt m Id

User · Answer

I was facing this same scaring error       Then I realized that I was forgetting to set a   Html HiddenFor model   gt  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

User · Answer

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  gt  0        context Users Attach orderer

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I ll throw this in just in case someone is running into this issue while working in a parallel loop   Parallel ForEach query  deet   gt        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   gt        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

User · Answer

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

User · Answer

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   lt telerik GridBoundColumn HeaderText  Shouldnt see  Display  false        UniqueName  Id  DataField  Id  gt   lt  telerik GridBoundColumn gt   lt telerik GridTemplateColumn HeaderText  Id  UniqueName  IdDisplay  gt       lt ItemTemplate gt           lt asp Label ID  IDLabel  runat  server               Text   lt    Eval  Id     gt   gt  lt  asp Label gt                                      lt  ItemTemplate gt   lt  telerik GridTemplateColumn gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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         lt asp BoundField DataField  Id primary key  ItemStyle-CssClass  hidden   HeaderStyle-CssClass  hidden    gt    Where  hidden  is a class in css that has it s display set to  none

User · Answer

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 lt Weather gt         public WeatherMap                             this Property t   gt  t TMax  HasColumnName  TMax            this Property t   gt  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 lt Weather gt         public WeatherMap                             this Property t   gt  t TMax  HasPrecision 5 2  HasColumnName  TMax            this Property t   gt  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

User · Answer

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 lt IHttpActionResult gt  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

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

Examples related to entity-framework