Entity framework update failure

9
votes

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'[email protected]',@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'
5
entity-framework
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
dbContext.Cases.Add(newCase) (it a new object, not an existing one) - user3559349
You are trying to update an object that does not exist in the database. - trailmax
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
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

5 Answers

8
votes

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.

4
votes

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.

3
votes

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.

0
votes

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.

0
votes

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 .