使用SQLite更新EF Core应用程序中的实体会提供DbUpdateConcurrencyException [英] Updating entity in EF Core application with SQLite gives DbUpdateConcurrencyException
问题描述
我尝试在带有SQLite的EF Core中使用开放式并发检查.最简单的积极情景(即使没有并发本身)也给了我 Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException:'数据库操作预期会影响1行，但实际上影响0行.自加载实体以来，数据可能已被修改或删除.
I try to use optimistic concurrency check in EF Core with SQLite.
The simplest positive scenario (even without concurrency itself) gives me
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.
实体:
public class Blog
{
public Guid Id { get; set; }
public string Name { get; set; }
public byte[] Timestamp { get; set; }
}
上下文:
internal class Context : DbContext
{
public DbSet<Blog> Blogs { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite(@"Data Source=D:\incoming\test.db");
///optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Blog>()
.HasKey(p => p.Id);
modelBuilder.Entity<Blog>()
.Property(p => p.Timestamp)
.IsRowVersion()
.HasDefaultValueSql("CURRENT_TIMESTAMP");
}
}
示例:
internal class Program
{
public static void Main(string[] args)
{
var id = Guid.NewGuid();
using (var db = new Context())
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
db.Blogs.Add(new Blog { Id = id, Name = "1" });
db.SaveChanges();
}
using (var db = new Context())
{
var existing = db.Blogs.Find(id);
existing.Name = "2";
db.SaveChanges(); // Exception thrown: 'Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException'
}
}
}
我怀疑这与EF和SQLite之间的数据类型有关.日志记录为我提供了有关更新的以下查询:
I suspect it's something to do with the data types between EF and SQLite. Logging gives me the following query on my update:
Executing DbCommand [Parameters=[@p1='2bcc42f5-5fd9-4cd6-b0a0-d1b843022a4b' (DbType = String), @p0='2' (Size = 1), @p2='0x323031382D31302D30372030393A34393A3331' (Size = 19) (DbType = String)], CommandType='Text', CommandTimeout='30']
UPDATE "Blogs" SET "Name" = @p0
WHERE "Id" = @p1 AND "Timestamp" = @p2;
但是ID和Timestamp的列类型均为BLOB(SQLite不提供UUID和timestamp列类型):
But the column types are BLOB for both Id and Timestamp (SQLite does not provide UUID and timestamp column types):
同时，如果我使用SQL Server(使用带注释的连接字符串+删除 .HasDefaultValueSql("CURRENT_TIMESTAMP"))，示例将正确运行并更新数据库中的时间戳.
At the same time if I use SQL Server (use commented connection string + remove .HasDefaultValueSql("CURRENT_TIMESTAMP")), sample works correctly and updates timestamp in the DB.
使用过的软件包:
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.1.4" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="2.1.4" />
我是否为并发检查配置了模型?这让我发疯，因为我无法在这种最简单的情况下使用它.
Have I configured the model for concurrency check wrong? That drives me crazy that I can't make it work with this simplest scenario.
更新:我最终是如何工作的.这里只显示了一个主意，但可能对任何人都有帮助
UPDATE: how I finally made it work. Here only idea is shown, but probably it helps anybody:
public class Blog
{
public Guid Id { get; set; }
public string Name { get; set; }
public long Version { get; set; }
}
internal class Context : DbContext
{
public DbSet<Blog> Blogs { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite(@"Data Source=D:\incoming\test.db");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Blog>()
.HasKey(p => p.Id);
modelBuilder.Entity<Blog>()
.Property(p => p.Version)
.IsConcurrencyToken();
}
}
internal class Program
{
public static void Main(string[] args)
{
var id = Guid.NewGuid();
long ver;
using (var db = new Context())
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
var res = db.Blogs.Add(new Blog { Id = id, Name = "xxx", Version = DateTime.Now.Ticks});
db.SaveChanges();
}
using (var db = new Context())
{
var existing = db.Blogs.Find(id);
existing.Name = "yyy";
existing.Version = DateTime.Now.Ticks;
db.SaveChanges(); // success
}
using (var db = new Context())
{
var existing = db.Blogs.Find(id);
existing.Name = "zzz";
existing.Version = DateTime.Now.Ticks;
db.SaveChanges(); // success
}
var t1 = Task.Run(() =>
{
using (var db = new Context())
{
var existing = db.Blogs.Find(id);
existing.Name = "yyy";
existing.Version = DateTime.Now.Ticks;
db.SaveChanges();
}
});
var t2 = Task.Run(() =>
{
using (var db = new Context())
{
var existing = db.Blogs.Find(id);
existing.Name = "zzz";
existing.Version = DateTime.Now.Ticks;
db.SaveChanges();
}
});
Task.WaitAll(t1, t2); // one of the tasks throws DbUpdateConcurrencyException
}
}
推荐答案
看起来像EF Core SQLite提供程序不能正确处理 [TimeStamp] (或 IsRowVersion())将它们绑定到SQL查询参数时标记为 byte [] 属性.它使用默认的 byte [] 进行 hex string 转换，在这种情况下不适用- byte [] 实际上是字符串.
Looks like EF Core SQLite provider does not handle properly [TimeStamp] (or IsRowVersion()) marked byte[] properties when binding them to SQL query parameters. It uses the default byte[] to hex string conversion which is not applicable in this case - the byte[] actually is a string.
首先考虑将其报告给他们的问题跟踪器.然后，直到解决(如果有)，作为一种解决方法，您可以使用以下自定义 ValueConverter :
First consider reporting it to their issue tracker. Then, until it gets resolved (if ever), as a workaround you can use the following custom ValueConverter:
class SqliteTimestampConverter : ValueConverter<byte[], string>
{
public SqliteTimestampConverter() : base(
v => v == null ? null : ToDb(v),
v => v == null ? null : FromDb(v))
{ }
static byte[] FromDb(string v) =>
v.Select(c => (byte)c).ToArray(); // Encoding.ASCII.GetString(v)
static string ToDb(byte[] v) =>
new string(v.Select(b => (char)b).ToArray()); // Encoding.ASCII.GetBytes(v))
}
不幸的是，没有办法告诉EF Core仅将其用于参数，因此在为它分配 .HasConversion(new SqliteTimestampConverter())后，现在将db类型视为 string，因此您需要添加 .HasColumnType("BLOB").
Unfortunately there is no way to tell EF Core to use it only for parameters, so after assigning it with .HasConversion(new SqliteTimestampConverter()), now the db type is considered string, so you need to add .HasColumnType("BLOB").
最终的工作映射是
modelBuilder.Entity<Blog>()
.Property(p => p.Timestamp)
.IsRowVersion()
.HasConversion(new SqliteTimestampConverter())
.HasColumnType("BLOB")
.HasDefaultValueSql("CURRENT_TIMESTAMP");
您可以通过在 OnModelCreating 的末尾添加以下自定义SQLite RowVersion常规"来避免所有这些情况:
You can avoid all that by adding the following custom SQLite RowVersion "convention" at the end of your OnModelCreating:
if (Database.IsSqlite())
{
var timestampProperties = modelBuilder.Model
.GetEntityTypes()
.SelectMany(t => t.GetProperties())
.Where(p => p.ClrType == typeof(byte[])
&& p.ValueGenerated == ValueGenerated.OnAddOrUpdate
&& p.IsConcurrencyToken);
foreach (var property in timestampProperties)
{
property.SetValueConverter(new SqliteTimestampConverter());
property.Relational().DefaultValueSql = "CURRENT_TIMESTAMP";
}
}
因此您的媒体资源配置可以精简为
so your property configuration could be trimmed down to
modelBuilder.Entity<Blog>()
.Property(p => p.Timestamp)
.IsRowVersion();
或完全删除并替换为数据注释
or totally removed and replaced with data annotation
public class Blog
{
public Guid Id { get; set; }
public string Name { get; set; }
[Timestamp]
public byte[] Timestamp { get; set; }
}
这篇关于使用SQLite更新EF Core应用程序中的实体会提供DbUpdateConcurrencyException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
