Concurrency exception when trying to insert entity with datetime2(0) key

[clement911]

Here is bug in EF6 Core.

Create an entity as follows:

public class QueryRun
    {
        [Column(TypeName = "datetime2(0)")]
        public DateTime StartedAt { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [NonDefault]
        public int Id { get; private set; }

        //other properties omitted for brevity
     }

Then try insert an entity

ctx.Add(new QueryRun
                {
                    StartedAt = DateTime.UtcNow,
                    //other properties omitted for brevity
                });
 await ctx.SaveChangesAsync();

This fails with the message: Expected 1 affected rows but got 0 rows!
After scratching my head for some time, I found the bug in the generated SQL code.

We can see that EF generated a param @p0 datetime2(7) instead of datetime2(0).
The issue is that the predicate [StartedAt] = @p0 returns FALSE because the parameter has milliseconds whereas the column is datetime2(0) and has no milliseconds.

Expected behaviour: when generating the p0 parameter, EF should use the same type as the column. That is, it should use datetime2(0) instead of datetime2(7). If I change the generated code to use datetime2(0), I can see that the generated ID is returned.

[roji]

@clement911 can you confirm that you've confirmed StartAt as a concurrency token? Assuming so, what technique are you using to update it? In any case, a fully runnable code sample (including the model) is always important for us to investigate, rather than isolated snippets.

[clement911]

@roji on, StartsAt is not a concurrency token.

I've created a fully runnable repro for you here: https://github.com/clement911/EFBug

[roji]

@clement911 thanks for the repro, makes sense - the missing piece above was that StartedAt was part of the key. Seems like when sending parameters in the update pipeline, we don't fully configure the parameter based on the column's type mapping.

Minimal repro

using var ctx = new MyDbContext();

ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();

ctx.Add(new MyEntity() { StartedAt = new DateTime(2000, 1, 1, 1, 1, 1, millisecond: 0) });
await ctx.SaveChangesAsync();//WORKS

ctx.Add(new MyEntity() { StartedAt = new DateTime(2000, 1, 1, 1, 1, 1, millisecond: 1) });
await ctx.SaveChangesAsync();//FAILS: The database operation was expected to affect 1 row(s), but actually affected 0 row(s);

public class MyEntity
{
    [Column(TypeName = "datetime2(0)")]
    public DateTime StartedAt { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; private set; }
}

public class MyDbContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<MyEntity>().HasKey(i => new { i.StartedAt, i.Id });
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Trust Server Certificate=true");
    }
}

[roji]

Duplicate of dotnet/SqlClient#1380

[roji]

This is because of a SqlClient issue specifically with scale 0 on datetime2, see dotnet/SqlClient#1380. This issue is specifically called out in their docs:

For the DateTime2 type, scale 0 (the default) will be passed as datetime2(7). There is currently no way to send a parameter as datetime2(0). Scales 1-7 work as expected. This problem applies to DateTimeOffset and Time as well.

So while EF Core properly sets the SqlParameter's scale to 0, that gets ignored by SqlClient, which sends the full, untruncated value. Since that doesn't match the database column value (which is truncated), a concurrency exception occurs.