efcore: EFCore Delete And Add will merge to Update,and throw concurrency Exception
i`m trying to update my code from ef6 to efcore . and get a concurrency exception
i have a Demo Entity
public class itemMap : IEntityTypeConfiguration<item>
{
public void Configure(EntityTypeBuilder<item> builder)
{
builder.HasKey(t => t.ItemGID);
builder.Property(t => t.ItemGID)
.IsRequired()
.HasMaxLength(100);
builder.Property(t => t.ItemID)
.IsRequired()
.HasMaxLength(100);
builder.ToTable("item");
builder.Property(t => t.ItemGID).HasColumnName("ItemGID");
builder.Property(t => t.ItemID).HasColumnName("ItemID");
builder.Property(t => t.TS)
.ValueGeneratedOnUpdate().IsConcurrencyToken();
}
}
DemoCode:
using (var context = new demoContext())
{
var defaultPrimaryKey = "test";
var data = context.Set<item>().Find(defaultPrimaryKey);
context.Set<item>().Remove(data);
var item1 = new item
{
ItemGID = defaultPrimaryKey,ItemID = defaultPrimaryKey,
TS = DateTime.Now
};
context.Set<item>().Add(item1);
context.SaveChanges();
}
this scene is when you call Set<T>.Remove and then call Set<T>.Add , and the entity primary key is same . 1. EFCore SaveChange will transfer to one update sql. 2. EF6 SaveChange will transfer to two sql,one is delete sql and another is insert sql. will , this is smart for EFCore ,but with ConcurrencyToken property , the problem is the update sql where condition TS is ‘NewValue’ not DatabaseValue , so get a concurrency exception. i think if efcore merge delete and insert to update , the ConcurrencyToken property value will use databaseValue , not the insert new value ? i had tested on sqlserver , and even get this error , so it is not problem for provider .
Exception message:
Microsoft.EntityFrameworkCore.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. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.”
LogSql is : Executed DbCommand (2ms) [Parameters=[@p3='test' (Nullable = false) (Size = 100), @p0='' (Size = 100), @p4='test' (Nullable = false) (Size = 100), @p1='' (Size = 100), @p2='2019-06-29T14:59:15' (DbType = DateTime), @p5='2019-06-29T14:59:15' (DbType = DateTime)], CommandType='Text', CommandTimeout='30']
UPDATE `item` SET `ItemDesc` = @p0, `ItemName` = @p1, `TS` = @p2
WHERE `ItemGID` = @p3 AND `ItemID` = @p4 AND `TS` = @p5;
SELECT ROW_COUNT();
@p5 is insert new value , so affected 0 row(s). if @p5 is databaseValue they will be ok.
Further technical details
EF Core version: Microsoft.EntityFrameworkCore 2.2.4 Database Provider: Pomelo.EntityFrameworkCore.MySql 2.2 Operating system: Windows 10 IDE: (e.g. Visual Studio 2019 16.1.3)
About this issue
- Original URL
- State: closed
- Created 5 years ago
- Comments: 17 (7 by maintainers)
@asulwer I verified that the repro in https://github.com/Gavinluo/EFCoreSameKeyException doesn’t throw exceptions on 3.1.16 or 5.0.7 for SQL Server
this is normal procedure for new bugs but not existing bugs that were reported as being fixed. it was reported here and this one needs to be reopened.
fyi: my example would be the same as this one. Remove, Add, SaveChanges, causes the error
this is still occurring in Core 5.0.7. maybe these programmers should check their code before they say its working?
Odd that this is shown as fixes in 3.0
I’m on 3.1 and experiencing a similar issue. I have 1 deleted entry, and 1 added entry in the change tracker (Removing an item from a collection, and adding a new one) and EF Core 3.1 sends an update instead which results in a
Database operation expected to affect 1 row(s) but actually affected 0 row(s)
exception.@Gavinluo The usual workaround is to make two calls to
SaveChanges
–one to do the remove, one to do the add.@AndriySvyryd @ajcvickers good job , I have already tested it on 3.0.0-preview6 and it works . but my project is work on netcore 2.2 , do you have a plan ? how should i to do with this problem?
This might be already fixed in 3.0.0-preview5