Entity framework update failure

Question

I want to update a row in EF:

Case newCase = new Case(...);
dbContext.Entry(newCase).State = EntityState.Modified;
dbContext.SaveChanges();

When updating I have this message:

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

There is no concurrency, since I am using the db on my machine.

If I go to SQL profiler to see the query that is executed, the condition is:

WHERE [Id] = @p0 AND [RowVersion] = @p14;

in this condition, @p0 is 1 and @p14 is NULL, exactly like the record that I want to modify. however, if I manually launch this query in SQL Server after removing the RowVersion part of the query, it succeeds, updating 1 row.

What can be the problem and how can I solve it?

EDIT:

I tried to select the record just before updating it, but the error is the same:

    IEnumerable<Case> cases = from c in dbContext.Cases where c.Id.ToString() == "1" select c;
    Case cs = cases.SingleOrDefault();
    dbContext.Entry(cs).State = EntityState.Modified;
    dbContext.SaveChanges();

Here is the complete error:

An exception of type 'Microsoft.Data.Entity.DbUpdateConcurrencyException' occurred in EntityFramework.Core.dll but was not handled in user code

Additional information: 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. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

And here is the query from Sql Profiler:

exec sp_executesql N'SET NOCOUNT OFF;

UPDATE [Case]
    SET 
        [Closed] = @p1
      , [Comment] = @p2
      , [ContactComment] = @p3
      , [ContactId] = @p4
      , [CreatedBy] = @p5
      , [CreatedDateTime] = @p6
      , [Description] = @p7
      , [Email] = @p8
      , [LastModifiedBy] = @p9
      , [LastModifiedDateTime] = @p10
      , [OpenedDateTime] = @p11
      , [Phone] = @p12
      , [RowVersion] = @p13
      , [SlaConsumedTime] = @p15
      , [SlaSuspended] = @p16
      , [SlaTotalTime] = @p17
      , [Status] = @p18
      , [Title] = @p19
WHERE 
    [Id] = @p0
    AND [RowVersion] = @p14;

SELECT @@ROWCOUNT;',

N'@p0 int,@p1 bit,@p2 nvarchar(max) ,@p3 nvarchar(max) ,@p4 int,@p5 int,@p6 datetime2(7),
@p7 nvarchar(4000),@p8 nvarchar(4000),@p9 int,@p10 datetime2(7),@p11 datetime2(7),
@p12 nvarchar(max) ,@p13 varbinary(max) ,@p14 varbinary(max) ,@p15 time(7),@p16 bit,
@p17 time(7),@p18 int,@p19 nvarchar(4000)',
@p0=1,@p1=0,@p2=NULL,@p3=NULL,@p4=0,@p5=1,@p6='2015-09-23 09:07:55.7041023',@p7=N'y',
@p8=N't@f.f',@p9=1,@p10='2015-09-23 09:50:02.9934006',@p11='2015-09-23 09:07:55.6796028',
@p12=NULL,@p13=NULL,@p14=NULL,@p15='00:00:00',@p16=0,@p17='00:00:00',
@p18=0,@p19=N'y'

your `Case` entity surely has a `RowVersion` property, this is used for concurrency check. But when you create a `Case` with `new`, that property ***does not have*** the original value currently stored in database. So the WHERE condition failed, you have to manually set the original values for `RowVersion` with some value fetched from database before calling `SaveChanges()`. — Hopeless, Sep 23 '15 at 09:23
`dbContext.Cases.Add(newCase)` (it a new object, not an existing one) — , Sep 23 '15 at 09:24
You are trying to update an object that does not exist in the database. — trailmax, Sep 23 '15 at 09:25
@StephenMuecke he wants to update an existing row, so I don't think Add would work. — Hopeless, Sep 23 '15 at 09:26
@Hopeless, Perhaps, but then why is OP initializing a new object? — , Sep 23 '15 at 09:27
@StephenMuecke it's another way of updating an existing record. That way he has to use `Attach`. He already said he has that record in database (tried with SQL query directly with `RowVersion` removed). — Hopeless, Sep 23 '15 at 09:31
@Hopeless, If OP is updating an existing record, then OP should be getting the existing record and updating the appropriate properties, then saving — , Sep 23 '15 at 09:33
@StephenMuecke yes, I always do that but maybe he has some reason to do so (the code here may be just some example). — Hopeless, Sep 23 '15 at 09:34
@Hopeless I also tried selecting the record from the database and then updating it, so the RowVersion field is exactly like it was, but this didn't fix the problem. — Sean, Sep 23 '15 at 09:35
@Sean if you tried something new, please add that to your question and also include the generated SQL query. Also the exact exception if thrown should be added. — Hopeless, Sep 23 '15 at 09:37
@Sean I can see `RowVersion` is still `NULL`. That's the same exception. Is there actually any `RowVersion` in the database table? it should not be null after being loaded from database. It's going to be strange now. — Hopeless, Sep 23 '15 at 09:55
Yes there is RowVersion in the table. The table is generated with Code First, this way: RowVersion = table.Column(isNullable: true) — Sean, Sep 23 '15 at 09:59
now do you have access to the created database? can you check if the `RowVersion` column has some non-null value? I guess it must be non-null (otherwise the query should have succeeded). If it's non-null, then after being loaded it should have also been non-null. That's why I said it's strange here. For debugging, you can try getting the property entry of `cs.RowVersion` like this `dbContext.Entry(cs).Property("RowVersion")` and see its `OriginalValue`. — Hopeless, Sep 23 '15 at 10:05
Yes RowVersion is null in the database for the record, and after selecting it I have this in Visual Studio: dbContext.Entry(cs).Property("RowVersion").CurrentValue = null — Sean, Sep 23 '15 at 10:34
if so the query failed reasonably (I was wrong when said the query should have succeeded) - because `RowVersion = NULL` will never be true, it should be `RowVersion is NULL`). But the query is auto-generated so we cannot easily make it be translated to `Is NULL` instead of `= NULL`. However I think you configure the model wrong, the `RowVersion` should not be nullable. Also somehow you should ensure it to be auto-generated whenever a new record is added/modified. I would use `IsRowVersion()` method to configure it. — Hopeless, Sep 23 '15 at 10:44
In fact I thought it would auto-generate the values of RowVersion. I tried doing RowVersion = new byte[] { 0x20 } when creating a new record, and after this the update worked. But I will try to understand this RowVersion Logic (if I don't add the column, Code First does not create the table). Thank you for your help. — Sean, Sep 23 '15 at 10:56

score 14 · Answer 1 · edited Oct 21 '16 at 07:27

14

Its due to, Primary Key as Null value to the LinQ.

Case newCase = new Case(...);
dbContext.Entry(newCase).State = EntityState.Modified;
dbContext.SaveChanges();

Check your object, does it pass Primary Key Null. This Exception is Generally due this reason only.

edited Oct 21 '16 at 07:27

Bojan B

2,091
4
18
26

answered Oct 21 '16 at 05:34

Jani Devang

1,099
12
20

That was it! Passing a null value is fine, as long as you configure autoincrement on your DB. – lenny Aug 17 '21 at 11:56

score 5 · Answer 2 · answered Sep 23 '15 at 09:23

5

your entity's primary key field means ID is not set.. means it is zero. As you are trying to update record so its ID key should have a value set the ID and update will work. Your update code is fine.

answered Sep 23 '15 at 09:23

Ankit Sahrawat

1,306
1
11
24

3

No the id is set, as I said the condition is WHERE [Id] = p0 and p0 = 1. – Sean Sep 23 '15 at 09:26

score 4 · Answer 3 · answered May 11 '20 at 01:12

4

In my case I created a new table in SQL and not with any migrations or anything. I forgot to set the id field to be a primary key and an identity(1,1). Once I put that in place there were no longer any issues saving.

answered May 11 '20 at 01:12

stimms

42,945
30
96
149

score 1 · Answer 4 · answered Jan 10 '20 at 18:51

I have table with composite primary key:

CREATE TABLE `MyTable` (
    `Key1` INT(11) NOT NULL,
    `Value` LONGBLOB NULL DEFAULT NULL,
    `Key2` INT(11) NOT NULL DEFAULT -1,
    PRIMARY KEY (`Key1`, `Key2`)
)

When I try to insert new row with Key2 = 0 it fails with same error.

DbUpdateConcurrencyException: '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.

My solution is to avoid saving 0 as key value. It seems to be treated as NULL and cannot be part of valid primary key. Hope this will save someone's time.

score 0 · Answer 5 · answered Mar 03 '21 at 00:35

0

In my case I delete the related table and save it ,therefore I had an error .I changed it and I deleted both table at first and then save both of them .

answered Mar 03 '21 at 00:35

Ali Mahdian

59
4

Entity framework update failure

5 Answers5

Linked