How to set another value on a boolean with defaut value with Entity Framework Core?

Question

I got quite the same problem in this question : How to override SQL Server default value constraint on a boolean when inserting new entity? [closed]

Like him, I get the good value of my boolean from the client to the controller, false, but it's set to true by the call of _context.SaveChanges(); because of Entity Framework and the default value constraint in the database.

BUT : I'm using Entity Framework Core and I don't have any [DatabaseGenerated(DatabaseGeneratedOption.Computed)] annotation to remove to fix the problem.

In my ApplicationDbContext.cs :

modelBuilder.Entity<myEntity>(entity =>
{
    entity.Property(e => e.Active).HasDefaultValueSql("1");
    //entity.Property(e => e.Active).HasDefaultValueSql<bool>("1"); // doesn't fix
    ...
}

In my Database :

CREATE TABLE myEntity(
    Id      INTEGER IDENTITY(1,1) NOT NULL,
    ...
    Active  BIT NOT NULL CONSTRAINT DF_myEntity_Active DEFAULT 1
);

In my Controller :

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Create([Bind("Id, Active, etc.")] Entity myEntity)
{
    if (ModelState.IsValid)
    {
        _context.Add(myEntity); // here myEntity.Active = false
        await _context.SaveChangesAsync();
        // same problem with _context.SaveChanges(); in another controller
        // here myEntity.Active = true
    }
    ...
}

It seems that EF doesn't map C# boolean with SQL bit correctly and always takes the default value. Does somebody have an issue to force the false value ?

Solution 1

Finally I can have data annotations generated in my EF model using --data-annotations option in the EF command. So I put [DatabaseGenerated(DatabaseGeneratedOption.None)] on the property and don't use the default contraint of the database.

Solution 2

I don't think you can set default values with annotations. However, I have found a rather tricky workaround after researching this issue myself.

If you set your bool value to be nullable, then use the fluent api to set the default, you should be fine. Its not perfect, but it works:

public class Year
{
        public int Id { get; set; }
        public string label { get; set; }
        public int year { get; set; }
        public bool? active { get; set; }
}

Then, in your data context, set the default value:

            modelBuilder.Entity<Year>()
            .Property("active")
            .HasDefaultValue(true);

When you insert new records into the database, you won't need to specify the boolean property in your object declaration. Below, the year 2017 will have a default value of true.

            var newYears = new List<Year>();

            newYears.Add(new Year { label = "2019", year = 2019, active = false });
            newYears.Add(new Year { label = "2018", year = 2018, active = true });
            newYears.Add(new Year { label = "2017", year = 2017});
            _context.Years.AddRange(newYears);
            _context.SaveChanges();

Solution 3

I have also faced almost similar issue with nullable bool value. I was using EF on top of Sqlite db. For me the update value true/false was not reflecting in the db. After I have overridden the OnModelCreating method and configured the property as below

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyEntity>()
        .Property(p => p.IsActive).ValueGeneratedNever().HasDefaultValue(null);
    }

After this changes as the values were updated as per expected. Hope this may help you as well.

Comments

[CicheR]

This also works for EF Core 6 when IsActive is not nullable and with .HasDefaultValue(true)