Integer value 0 is not set on INSERT when field has different default value

[DJDoena]

When using SQL Server DB-first approach and defining a non-nullable integer field with default value 42:

CREATE TABLE [dbo].[DefaultValue](
	[Id] [int] NOT NULL,
	[SomeText] [varchar](50) NOT NULL,
	[IntWithDefault] [int] NOT NULL,
 CONSTRAINT [PK_DefaultValue] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DefaultValue] ADD  CONSTRAINT [DF_DefaultValue_IntWithDefault]  DEFAULT ((42)) FOR [IntWithDefault]
GO

then running a scaffold

Scaffold-DbContext "Server=XXX;Database=XXX;user id=XXX;password=XXX;Encrypt=False;" Microsoft.EntityFrameworkCore.SqlServer -Context PlaygroundEntities -NoPluralize -Force -UseDatabaseNames

the generated code for that table looks like this:

modelBuilder.Entity<DefaultValue>(entity =>
{
    entity.Property(e => e.Id).ValueGeneratedNever();
    entity.Property(e => e.IntWithDefault).HasDefaultValueSql("((42))");
    entity.Property(e => e.SomeText)
        .HasMaxLength(50)
        .IsUnicode(false);
});

When trying to add a new row with a value of 0 for field IntWithDefault the generated INSERT statement omits the field altogether.

using var context = new PlaygroundEntities();

var e = new DefaultValue()
{
    Id = 5,
    SomeText = "Hello",
    IntWithDefault = 0,
};

context.DefaultValue.Add(e);

context.SaveChanges();

warn: 13.03.2023 12:44:14.315 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure)
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: 13.03.2023 12:44:16.578 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (113ms) [Parameters=[@p0='5', @p1='Hello' (Nullable = false) (Size = 50) (DbType = AnsiString)], CommandType='Text', CommandTimeout='30']
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      INSERT INTO [DefaultValue] ([Id], [SomeText])
      OUTPUT INSERTED.[IntWithDefault]
      VALUES (@p0, @p1);

Any other value, including the database default of 42 produces the correct INSERT statement:

var e = new DefaultValue()
{
    Id = 6,
    SomeText = "Hello",
    IntWithDefault = 42,
};

warn: 13.03.2023 12:46:20.495 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure)
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: 13.03.2023 12:46:22.083 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (76ms) [Parameters=[@p0='6', @p1='42', @p2='Hello' (Nullable = false) (Size = 50) (DbType = AnsiString)], CommandType='Text', CommandTimeout='30']
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      INSERT INTO [DefaultValue] ([Id], [IntWithDefault], [SomeText])
      VALUES (@p0, @p1, @p2);

Microsoft Visual Studio Professional 2022 (64-bit) - Current Version 17.4.4

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.3">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.3" />
  </ItemGroup>

</Project>

[ajcvickers]

This is currently by-design--see #13613 and #15070.

[DJDoena]

@ajcvickers

This is currently by-design--see #13613 and #15070.

And how do I mitigate this in the meantime? We've just migrated from full framework 4.7.2 with EF6 to net6 with EF7 and we have quite larget databases with default values in them.

[ajcvickers]

@DJDoena When do you want the database default value to be used? That is, if the property is set to some value, then do you want that value to be used instead of the default? Or do you want the database default value to always be used, regardless of what value the property has?

[DJDoena]

@DJDoena When do you want the database default value to be used? That is, if the property is set to some value, then do you want that value to be used instead of the default? Or do you want the database default value to always be used, regardless of what value the property has?

@ajcvickers Basically the default values are from a bygone era but due to very specific audit reasons the DB cannot be easily changed. But if I set a value in my object I want it to be found in the DB later on. I understand the difficulty to distinguish between a 0 that is the CLR default and a 0 that I have set, so for the moment it would be sufficient to always overrule the DB default and take what comes in the statements. Because the old full framework EF couldn't deal with default values either and all properties had to be properly set anyways.

[ajcvickers]

@DJDoena Configure the property with .ValueGeneratedNever();.