CODEWITHSUNDEEP
entity-framework
Sean
Sean

Reputation: 969

Entity framework update failure

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'

Upvotes: 13

Views: 55358

Answers (5)

Ali Mahdian
Ali Mahdian

Reputation: 139

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 .

Upvotes: 0

stimms
stimms

Reputation: 44046

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.

Upvotes: 4

oneekz
oneekz

Reputation: 41

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.

Upvotes: 1

Jani Devang
Jani Devang

Reputation: 1109

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.

Upvotes: 14

Ankit K
Ankit K

Reputation: 1326

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.

Upvotes: 5

Related Questions