Optimistic Concurrency with IsRowVersion()

[Unlink]

I cannot use Optimistic Concurrency mode with Oracle driver.

I have DateTime property marked as RowVersion builder.Property(e => e.LastUpdate).IsRowVersion(); It creates table with non null LastUpdate column and when i add some entity to DB it fails with ORA-01400: cannot insert NULL into ("dbuser"."Blog"."LastUpdate")

The query looks like this:

DECLARE
TYPE "rBlog_0" IS RECORD
(
"LastUpdate" TIMESTAMP(7)
);
TYPE "tBlog_0" IS TABLE OF "rBlog_0";
"lBlog_0" "tBlog_0";
v_RowCount INTEGER;
BEGIN

"lBlog_0" := "tBlog_0"();
"lBlog_0".extend(1);
INSERT INTO "Blog" ("ID", "Description", "Content")
VALUES (:p0, :p1, :p2)
RETURNING "LastUpdate" INTO "lBlog_0"(1)."LastUpdate";
OPEN :cur1 FOR SELECT "lBlog_0"(1)."LastUpdate" FROM DUAL;
END;

It should be related to: https://github.com/dotnet/efcore/issues/19765

[alexkeh]

Are you using HasDefaultValueSql so that a non-NULL entry will be inserted?

[Unlink]

If i use HasDefaultValueSql("SYSDATE") insert works but optimistic concurrency behavior is not working because LastUpdate is not updating automatically.

IsRowVersion is just alias for

Builder.ValueGenerated(ValueGenerated.OnAddOrUpdate, ConfigurationSource.Explicit);
Builder.IsConcurrencyToken(true, ConfigurationSource.Explicit);

and issue is that LastUpdated is not generated automatically by database or entity framework. Update query looks like this.

DECLARE
v0_LastUpdate TIMESTAMP(7);
v_RowCount INTEGER;
BEGIN
UPDATE "Blog" SET "Description" = :p0
WHERE "ID" = :p1 AND "LastUpdate" = :p2
RETURN "LastUpdate" INTO v0_LastUpdate;
v_RowCount := SQL%ROWCOUNT;
OPEN :cur1 FOR
SELECT v0_LastUpdate
FROM DUAL
WHERE v_RowCount = 1;

The query looks same also if I manualy set property LastUpdate to DateTime.Now. So it means that if the property is Builder.ValueGenerated(ValueGenerated.OnAddOrUpdate, ConfigurationSource.Explicit); ef does not update its value during saveChanges() method call.

In mysql when i use IsRowVersion it generates column as

`LastUpdate` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

which works as intended.

[alexkeh]

Looks like a bug. Do you have a test case I can use? I'll file the issue.

[Unlink]

My model look like this:

public class Blog
{
	public Guid Id { get; set; }
	public DateTime LastUpdated { get; set; }
	public string Name { get; set; } = "";
}

public class TestDbContext : DbContext
{

	public DbSet<Blog> Blogs { get; set; } = default!;
        public TestDbContext (DbContextOptions<TestDbContext > options)
		    : base(options)
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		base.OnConfiguring(optionsBuilder);
		optionsBuilder.UseOracle(...);
	}

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
		modelBuilder.Model.SetMaxIdentifierLength(30);
		modelBuilder.ApplyConfigurationsFromAssembly(typeof(TestDbContext).Assembly);
	}
}

        internal class BlogEntityConfig : IEntityTypeConfiguration<Blog>
	{
		public void Configure(EntityTypeBuilder<Blog> builder)
		{
			builder.HasKey(m => m.Id);
			builder.Property(m => m.LastUpdated).HasDefaultValueSql("SYSDATE").IsRowVersion();
			builder.Property(m => m.Name)
				.HasMaxLength(100);
		}
	}

Test case:

            [Fact]
	    public async Task TestTest()
	    {

		    var blog = new Blog
		    {
				Name = "Test"
		    };

		    using (var context = new TestDBContext())
		    {
			    context.Database.EnsureDeleted();
			    context.Database.EnsureCreated();
			    await context.Blogs.AddAsync(blog);
			    await context.SaveChangesAsync();

			    var lastUpdated = blog.LastUpdated;
			    blog.Name = "Test2";
			    await context.SaveChangesAsync();

			    var lastUpdated2 = blog.LastUpdated;


			    blog.Name = "Test3";
				blog.LastUpdated = DateTime.Now;
				await context.SaveChangesAsync();

			    var lastUpdated3 = blog.LastUpdated;
			}
	    }

SQL queries performed during test case:

BEGIN 
EXECUTE IMMEDIATE 'CREATE TABLE 
"Blogs" (
    "Id" RAW(16) NOT NULL,
    "LastUpdated" TIMESTAMP(7) DEFAULT (SYSDATE) NOT NULL,
    "Name" NVARCHAR2(100) NOT NULL,
    CONSTRAINT "PK_Blogs" PRIMARY KEY ("Id")
)';
END;

DECLARE
TYPE "rBlogs_0" IS RECORD
(
"LastUpdated" TIMESTAMP(7)
);
TYPE "tBlogs_0" IS TABLE OF "rBlogs_0";
"lBlogs_0" "tBlogs_0";
v_RowCount INTEGER;
BEGIN

"lBlogs_0" := "tBlogs_0"();
"lBlogs_0".extend(1);
INSERT INTO "Blogs" ("Id", "Name")
VALUES (:p0, :p1)
RETURNING "LastUpdated" INTO "lBlogs_0"(1)."LastUpdated";
OPEN :cur1 FOR SELECT "lBlogs_0"(1)."LastUpdated" FROM DUAL;
END;

DECLARE
v0_LastUpdated TIMESTAMP(7);
v_RowCount INTEGER;
BEGIN
UPDATE "Blogs" SET "Name" = :p0
WHERE "Id" = :p1 AND "LastUpdated" = :p2
RETURN "LastUpdated" INTO v0_LastUpdated;
v_RowCount := SQL%ROWCOUNT;
OPEN :cur1 FOR
SELECT v0_LastUpdated
FROM DUAL
WHERE v_RowCount = 1;

END;

DECLARE
v0_LastUpdated TIMESTAMP(7);
v_RowCount INTEGER;
BEGIN
UPDATE "Blogs" SET "Name" = :p0
WHERE "Id" = :p1 AND "LastUpdated" = :p2
RETURN "LastUpdated" INTO v0_LastUpdated;
v_RowCount := SQL%ROWCOUNT;
OPEN :cur1 FOR
SELECT v0_LastUpdated
FROM DUAL
WHERE v_RowCount = 1;

END;

Both updates are same, LastUpdated is not changed even if I explicitly change its value.

[alexkeh]

Thanks. I opened bug 31965621 to track this issue.

[mihaimyh]

Any updates on this?

[engsky]

Is there update on this??

[askdong]

Is there any progress on this?

[alexkeh]

The bug is fixed and will appear in the next ODP.NET patch on NuGet Gallery.

[alexkeh]

Fixed with Oracle EF Core 8