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

Question

I found a solution for people who get an exception:

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.

But, anyway I have question.

I read topic: Entity Framework: "Store update, insert, or delete statement affected an unexpected number of rows (0)." To VMAtm, Robert Harvey

In my case I had for example table articles:

Articles
------------
article_id
title
date_cr
date_mod
deleted

And I had trigger:

create trigger articles_instead_of_insert 
on articles instead of insert 
as      
    SET NOCOUNT ON;
    insert into articles(
        article_id, 
        title, 
        date_cr,
        date_mod, 
        deleted
    )
    select 
        user_id, 
        title, 
        isnull(date_cr, {fn NOW()}),
        isnull(date_mod, {fn NOW()}),
        isnull(deleted, 0)
    from inserted;
go

When I delete this trigger then I dont get this exception. So this trigger is problem. And now I have a question - Why? Should I do something?

Great VMAtm Robert, Maby you should read all my post before close? I read this post that you give a link to. — nosbor, Jul 25 '11 at 17:20
It's not an exact duplicate. The other one has answers about optimistic concurrency. The problem here is that the trigger should include a `SET NOCOUNT ON` statement or EF will be confused. — Anders Abel, Jul 25 '11 at 17:30
I change my trigger but I had still the same exception. I put SET NOCOUNT ON after as statement. Look my edit. — nosbor, Jul 25 '11 at 17:42
Why do you have a trigger at all? Looking at it, it appears all you really need are default values for date_cr, date_mod,and deleted. Defaults tend to perform better than triggers and should be used if they can be. I'd probably also use IsDeleted for the columnname instead as deleted is the name of a trigger pseudotable and it's confusing especially in a trigger. — HLGEM, Jul 27 '11 at 18:50
You're right in this case. But this trigger I wrote here is only example. My real trigger is different. That should only highlight the problem. But not trigger was the problem; that was only conjuncture. — nosbor, Jul 28 '11 at 15:19
Seeing the question and later comments: voting to close as not reproducible. The question doesn't reflect the real problem. — Gert Arnold, Jun 20 '20 at 15:03

score 38 · Accepted Answer · answered Jul 27 '11 at 17:37

38

Solution:

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

answered Jul 27 '11 at 17:37

nosbor

2,826
3
39
63

3

Refresh method resolves concurency conflicts be refreshing the object context. The first parameter tell which values should win. Please refer to http://msdn.microsoft.com/library/bb896255(v=vs.110).aspx – nosbor Sep 26 '14 at 08:03
11

I was having this issue too, but couldn't find `Refresh`. This helped: http://stackoverflow.com/questions/13177718/dbcontext-does-not-contain-a-definition-for-refresh – GotDibbs Oct 27 '14 at 20:03
hmm... I think solution would be to use lock mechanism rather just overwriting values – Prokurors Jun 12 '15 at 11:54
Some additional explanation in this answer: http://stackoverflow.com/a/11772953/12484 – Jon Schneider Sep 11 '15 at 21:05

score 6 · Answer 2 · edited Feb 24 '17 at 08:37

Its better you update your save method like this..... In case you calling savechange() method of entity context after every addobject and deleteobject or modification :

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

    }
}

score 3 · Answer 3 · edited Oct 04 '11 at 19:13

3

It's because you have SET NOCOUNT ON.
The EF SQL Statement that it generates ALWAYS adds a clause of where @@ROWCOUNT > 0 and [ID] = scope_identity() (for example).
Notice the where @@ROWCOUNT > 0 clause. Take off your SET NOCOUNT ON statement and it should work.

edited Oct 04 '11 at 19:13

Christian Specht

35,843
15
128
182

answered Oct 04 '11 at 15:45

Tim

121
1
2

Did you read comments? There was written that I used set nocount on and before it I didnt used it. – nosbor Oct 04 '11 at 17:14
I just ran into the same problem, however, removing the SET NOCOUNT ON didn't help. – Danny Varod Dec 21 '11 at 17:14

Max CHien · Answer 4 · 2019-06-26T06:32:01.717

In my case, need to set DatabaseGenerated Attribute when Custom Code First Conventions

public class Car
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    public string Model { get; set; }
    public DateTime Registered { get; set; }
}

or

Switching off Identity for Numeric Primary Keys The following example sets the DepartmentID property to System.ComponentModel.DataAnnotations.DatabaseGeneratedOption.None to indicate that the value will not be generated by the database.

modelBuilder.Entity<Department>().Property(t => t.DepartmentID)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

see https://learn.microsoft.com/zh-tw/ef/ef6/modeling/code-first/conventions/custom

and https://www.learnentityframeworkcore.com/configuration/data-annotation-attributes/databasegenerated-attribute

and https://learn.microsoft.com/zh-tw/ef/ef6/modeling/code-first/fluent/types-and-properties

score 1 · Answer 5 · answered May 16 '12 at 15:46

Possible Problem: You placed a ReadOnlyAttribute in you entity key's metadata, which causes its value to become zero

 [DisplayName("Student ID")]
 [ReadOnly(true)]
 public int StudentID { get; set; }

Solution: Remove the ReadOnlyAttribute

 [DisplayName("Student ID")]
 public int StudentID { get; set; }

score 1 · Answer 6 · edited May 23 '17 at 12:18

1

I had the same problem, this error message is a rather mystifying one. The answer from webtrifusion helped me. See Entity Framework: "Store update, insert, or delete statement affected an unexpected number of rows (0)."

It turns out I forgot to add "Id:0" in the JSON on the client side.

edited May 23 '17 at 12:18

Community

1
1

answered Sep 05 '14 at 10:06

binjiezhao

567
8
12

score 1 · Answer 7 · edited Mar 13 '17 at 15:23

I had InsertAsync setting EntityState to Modified implementing a Repository pattern. When Debugging I spotted the entity's id was 0

    public async Task InsertAsync(T entity)
    {

        dbContext.Entry(entity).State = EntityState.Modified;
        await dbContext.SaveChangesAsync();
    }

Changing the Entity State to Added fixed it.

    dbContext.Entry(entity).State = EntityState.Added;

score 1 · Answer 8 · answered May 18 '17 at 06:59

1

I had same error then I realized that I forgot the set primary key in table and setting increment values...

answered May 18 '17 at 06:59

Kemal Can ÖZÇELİK

724
7
12

score 0 · Answer 9 · answered Jul 20 '15 at 07:14

0

I found this error when i updating entity and forgot to pass Primary key value....

So Entity can update record with reference with Primary key

Solution : Check whether primary key value is pass or not to update the given record. :)

answered Jul 20 '15 at 07:14

Prashant J

1
2

This isn't a complete answer to the OP's question. – PC Luddite Jul 20 '15 at 07:26

score 0 · Answer 10 · answered Jun 20 '20 at 14:49

When using RowVersion as Tracking Property, to control concurrency in multi-user system, RowVersion which is auto generated by database engine by default, should be passed as Hidden Field in the view when Editing/Updating a record, the same way we manage the Key Identity Field:

@using (Html.BeginForm()) {
    @Html.HiddenFor(model => model.ID)
    @Html.HiddenFor(model => model.RowVersion) 
}

score -1 · Answer 11 · edited Sep 03 '13 at 10:40

-1

Using Entity Framework Code first,

using (MyDbContext db = new MyDbContext ( )) {..

Adding this after the using line:

db.Configuration.ValidateOnSaveEnabled = true;

edited Sep 03 '13 at 10:40

Sam M

1,077
2
20
42

answered Apr 23 '13 at 21:58

QT-1

900
14
21

The default value is true already, From MSDN: "Gets or sets a value indicating whether tracked entities should be validated automatically when SaveChanges is invoked. The default value is true." – usefulBee Nov 18 '15 at 21:09

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

11 Answers11

Linked