Issue with EF Core 5 HasDefaultValue, ef pushes default value when value is set to the .Net type def-CodePudding

I'm having a weird issue using entity framework core to insert records with default values columns. Is there an other way to get the expected behavior, am I missing something stupid?

tldr: setting an int property = 0 or bool to true results in the default values being used string.empty for the new string gives an ORA-01400: cannot insert NULL (this seems to be an oracle thing)

Scenario's: using a migration to add the new columns and adding new records results in normal behavior (default values are inserted)

adding a new record only oldField supplied= OK "ID" : 6, "OLDFIELD" : "ef test 1", "NEWINT" : 5, "NEWSTRING" : "def", "NEWBOOLFALSE" : 0, "NEWBOOLTRUE" : 1

adding with a value different than .Net default works too "ID" : 12, "OLDFIELD" : "ef test all set", "NEWINT" : 42, "NEWSTRING" : "string here", "NEWBOOLFALSE" : 1, "NEWBOOLTRUE" : 1

Now the problems when you specify 0 for an int and false for the bools

var test = new TestDef()
        {
            OldField = "ef bad test",
            NewInt = 0,
            NewBoolFalse = false,
            NewBoolTrue = false
        };
_womaDbContext.Add(test);
await _womaDbContext.SaveChangesAsync();

you get "ID" : 36, "OLDFIELD" : "ef bad test", "NEWINT" : 5, "NEWSTRING" : "def", "NEWBOOLFALSE" : 0, "NEWBOOLTRUE" : 1

NewString = string.Empty gives an oracle exception ORA-01400: cannot insert NULL (this seems to be an oracle thing)

insert query works normally

INSERT INTO WOMA_SCHEMA.TESTDEF
(ID, OLDFIELD, NEWINT, NEWBOOLFALSE, NEWBOOLTRUE)
VALUES("WOMA_SCHEMA"."ISEQ$$_42048".nextval, 'sql empty',0, 0,0);

This makes it impossible to insert a false value in NEWBOOLTRUE or a 0 in NEWINT without doing an update

testclass (the DbContext OnModelCreating calls TestDef.OnModelCreating(modelBuilder)):

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

namespace Test.Model
{
    [Table("TESTDEF")]
    public class TestDef
    {
        [Column("ID")]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Column("OLDFIELD")]
        public string OldField { get; set; }

        [Required]
        [Column("NEWINT")]
        public int NewInt { get; set; }

        [Required]
        [Column("NEWSTRING")]
        public string NewString { get; set; }

        [Required]
        [Column("NEWBOOLTRUE")]
        public bool NewBoolTrue { get; set; }

        [Required]
        [Column("NEWBOOLFALSE")]
        public bool NewBoolFalse { get; set; }

        public static void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Default values
            modelBuilder
                .Entity<TestDef>()
                .Property(e => e.NewInt)
                .HasDefaultValue(5);

            modelBuilder
                .Entity<TestDef>()
                .Property(e => e.NewString)
                .HasDefaultValue("def");

            modelBuilder
                .Entity<TestDef>()
                .Property(e => e.NewBoolTrue)
                .HasDefaultValue(true);

            modelBuilder
                .Entity<TestDef>()
                .Property(e => e.NewBoolFalse)
                .HasDefaultValue(false);
        }
    }
}

CodePudding user response：

This is well known behavior/defect of EF Core default values, since properties with CLR default (0 for numerics, false for bool, DateTime.Empty etc.) are not send as part of the insert command, hence the server applies the configured default value.

Before EFC 5.0 there was not solution other than not using database default values. Starting with EFC 5.0, the workaround is to use nullable backing fields as explained in Using nullable backing fields, Nullable backing fields for bool properties and Schema defaults only documentation topics (strangely hidden inside Working with default values subsection of Additional Change Tracking Features section).

So the idea is to use nullable backing field for such properties, e.g.

private int? _newInt;
[Required]
[Column("NEWINT")]
public int NewInt { get => _newInt ?? 0; set => _newInt = value; }

private bool? _newBoolTrue; 
[Required]
[Column("NEWBOOLTRUE")]
public bool NewBoolTrue { get => _newBool ?? true; set => _newBool = value; }