HasData with float PKs produces unnecessary migrations

[archanasoni]

Hi,

While supporting HasData(...) via IBM EntityFramework core provider, we observed that when table named TabA is having column type as real, and we insert some value via HasData(..) first time it generates insert command which is expected.
But next when we perform migration on any other table (for example Tabb) then also delete followed by insert is being generated for SQL server for the TabA. where as for DB2 it's update command for TabA.

Now our question is why is this behavior, When the same value only we are going to insert or update?

[ajcvickers]

@archanasoni Can you post the code for an example where this happens, including the entity types and configuration.

[archanasoni]

Hi,
Below example is for SQL Server, We have entity class:

public class IdentTab2
{
        //[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }
        [Key]

        public float? Colreal { get; set; }
        public int ID1 { get; set; }
        public string name { get; set; }
        public int defaultCol { get; set; }
        public int defaultCol1 { get; set; }
        public DateTime? defValDate { get; set; }
        public byte[] Colbinary { get; set; }
}

And Context class as:

public class MyDbContext : DbContext
{
    public MyDbContext()
    {
    }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {

        optionsBuilder.UseSqlServer(<connection string>);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<IdentTab2>(entity =>
        {
            entity.Property(e => e.Colreal)
                .HasColumnName("COLREAL")
                .HasColumnType("real");
            entity.Property(e => e.defValDate).HasDefaultValue(new DateTime(2018, 03, 02));
        });
        modelBuilder.Entity<IdentTab2>().HasData(new IdentTab2 { ID = 1, ID1 = 2, Colreal = 3.4028234663852886e+38F, Colbinary = new byte[] { 97, 98 }, defValDate = new DateTime(23 / 02 / 2019) });
    }
    
    public virtual DbSet<IdentTab2> IdentTab2s { get; set; }
    //public DbSet<Product> Products { get; set; }
}

now when we run
add-migration v1

it generates migration perfectly.

Now we will add another entity class named as Product

[Table("product")]
    public class Product
    {
        [Key]
        public int id { get; set; }
        public string name { get; set; }
    }

And updated context class with the following entry

public DbSet<Product> Products { get; set; }

Again when we run add-migration V2, it generates below code:

 protected override void Up(MigrationBuilder migrationBuilder)
        {
            **migrationBuilder.DeleteData(
                table: "IdentTab2s",
                keyColumn: "COLREAL",
                keyValue: 3.402823E+38f);**

            migrationBuilder.CreateTable(
                name: "product",
                columns: table => new
                {
                    id = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                    name = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_product", x => x.id);
                });

            **migrationBuilder.InsertData(
                table: "IdentTab2s",
                columns: new[] { "COLREAL", "Colbinary", "ID", "ID1", "defValDate", "defaultCol", "defaultCol1", "name" },**
                values: new object[] { 3.402823E+38f, new byte[] { 97, 98 }, 1, 2, new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified), 0, 0, null });
        }

Here in second migration, we are not touching even IdentTab2 but still it's generating delete and insert operation for it.

[ajcvickers]

@archanasoni What's happening here is that the primary key value is not being correctly preserved in the model snapshot such that it appears to be different when compared against the current primary key value. This in turn means that EF should delete the old row and insert a new one.

This in of itself is not very interesting given that it would be pretty ridiculous to have a primary key value of 3.4028234663852886e+38F. However, even if this was not the primary key value, it could still cause erroneous update commands due to the model differ thinking that the data has changed. Therefore, marking this as a bug to consider:

• Ensuring values round-trip correctly where possible
• Possibly using an approximate match in some cases where round-tripping won't work.

[bricelam]

I'm not able to repro this on 3.1.7. I'll assume that it was fixed as part of another issue at some point.