Hi,
using
.NET Core 3.1
Microsoft.EntityFrameworkCore 3.1.0
IBM.EntityFrameworkCore-osx 3.1.0.200
If I create a DbContext and use Add/SaveChanges, everything works and the new row is added to the table as expected. If I use AddAsync and SaveChangesAsync, I get a DbUpdateConcurrencyException, and no row is added to the table.
(This code uses a table from IdentityServer4, but this also applies to ASP.NET Identity etc.)
Unhandled exception. System.AggregateException: One or more errors occurred. (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.)
---> 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.
at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyException(Int32 commandIndex, Int32 expectedRowsAffected, Int32 rowsAffected)
at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithoutPropagationAsync(Int32 commandIndex, RelationalDataReader reader, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at eftest.Program.Perform() in /Users/xxx/projects/eftest/Program.cs:line 35
DDL for table:
create table PERSISTEDGRANTS
(
KEY VARCHAR(200) not null
constraint PK_PERSISTEDGRANTS
primary key,
TYPE VARCHAR(50) not null,
SUBJECTID VARCHAR(200),
SESSIONID VARCHAR(100),
CLIENTID VARCHAR(200) not null,
DESCRIPTION VARCHAR(200),
CREATIONTIME TIMESTAMP(6) not null,
EXPIRATION TIMESTAMP(6),
CONSUMEDTIME TIMESTAMP(6),
DATA VARCHAR(32672) not null
);
create index IX_PERSISTEDGRANTS_EXPIRATION
on PERSISTEDGRANTS (EXPIRATION);
create index IX_PERSISTEDGRANTS_SUBJECTID_CLIENTID_TYPE
on PERSISTEDGRANTS (SUBJECTID, CLIENTID, TYPE);
create index IX_PERSISTEDGRANTS_SUBJECTID_SESSIONID_TYPE
on PERSISTEDGRANTS (SUBJECTID, SESSIONID, TYPE);
My DbContext:
using System;
using IBM.EntityFrameworkCore;
using IBM.EntityFrameworkCore.Storage.Internal;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace eftest
{
public class PersistedGrantDbContext : DbContext
{
public DbSet<PersistedGrant> PersistedGrants { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connectionString = "REDACTED";
optionsBuilder.UseDb2(connectionString, db2Builder =>
{
db2Builder.SetServerInfo(IBMDBServerType.LUW, IBMDBServerVersion.LUW_11_01_2020);
db2Builder.UseRowNumberForPaging();
db2Builder.MaxBatchSize(1);
});
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<PersistedGrant>(grant =>
{
grant.ToTable("PERSISTEDGRANTS");
grant.Property(x => x.Key).HasMaxLength(200).ValueGeneratedNever();
grant.Property(x => x.Type).HasMaxLength(50).IsRequired();
grant.Property(x => x.SubjectId).HasMaxLength(200);
grant.Property(x => x.SessionId).HasMaxLength(100);
grant.Property(x => x.ClientId).HasMaxLength(200).IsRequired();
grant.Property(x => x.Description).HasMaxLength(200);
grant.Property(x => x.CreationTime).IsRequired();
grant.Property(x => x.Data).HasMaxLength(50000).IsRequired();
grant.HasKey(x => x.Key);
grant.HasIndex(x => new {x.SubjectId, x.ClientId, x.Type});
grant.HasIndex(x => new {x.SubjectId, x.SessionId, x.Type});
grant.HasIndex(x => x.Expiration);
});
}
}
public class PersistedGrant
{
public string Key { get; set; }
public string Type { get; set; }
public string SubjectId { get; set; }
public string SessionId { get; set; }
public string ClientId { get; set; }
public string Description { get; set; }
public DateTime CreationTime { get; set; }
public DateTime? Expiration { get; set; }
public DateTime? ConsumedTime { get; set; }
public string Data { get; set; }
}
}
My Program.cs:
using System;
using System.Threading.Tasks;
namespace eftest
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Hello World!");
Perform().Wait();
}
private static async Task Perform()
{
using (var context = new PersistedGrantDbContext())
{
var std = new PersistedGrant()
{
Data = "data",
Description = "descr",
Expiration = DateTime.Today,
Key = "Key",
Type = "type",
ClientId = "clientid",
ConsumedTime = DateTime.Today.AddDays(1),
CreationTime = DateTime.Now,
SessionId = "sesionid",
SubjectId = "subjectid"
};
await context.PersistedGrants.AddAsync(std);
await context.SaveChangesAsync();
}
}
}
}
Why is this?
If I add a dummy column like this to the original table above:
dummy timestamp not null generated always for each row on update as row change timestamp;
and then specify it is the row version in the DbContext, it works with both async and sync. But this makes it hard/impossible to use in a lot of cases when we can't change the model or DDL of the table.
Please help :-)
------------------------------
Best regards,
Lars
------------------------------
#Db2