Entity Framework Code first making a column non-nullable

Question

I am using EF code first for my project. I have following code in my DataModel

[HiddenInput(DisplayValue = false)]        
public DateTime? PasswordDate { get; set; }

To make this non-nullable I removed '?' and ran Add-Migration command from Package manager console. following migration file was generated.

  public partial class PasswordDate : DbMigration
{
    public override void Up()
    {
        AlterColumn("dbo.CertificateInfoes", "PasswordDate", c => c.DateTime(nullable: false));
    }

    public override void Down()
    {
        AlterColumn("dbo.CertificateInfoes", "PasswordDate", c => c.DateTime());
    }
}

But when I run Update-Database command:

Update-Database -SourceMigration 201309020721215_PasswordDate

I get following error: Cannot insert the value NULL into column 'PasswordDate', table ''; column does not allow nulls. UPDATE fails. The statement has been terminated.

Kindly suggest the solutions.

Answer 1

That's because you allowed NULL values in that column, then tried to make it non-nullable. It will subsequently try to migrate your existing data into that newly non-nullable column, which will break because you already have NULL values in there.

Two solutions:

1) Change it back to nullable
2) Give it a default value for items that don't have a value.

Answer 2

It's not possible to directly add a non-nullable column to a table that has historical data in the table if no default value is provided for that column.

What I do is

1. add the column as nullable.
2. provide an sql script to populate this newly added column.
3. alter the column to make is as non-nullable.

Code example(with postgres database):

 public override void Up()
    {            
        AddColumn("public.YourTableName", "YourColumnName", c => c.Int(nullable: true));
        Sql(@"UPDATE ""public"".""YourTableName""
              SET ""YourColumnName"" = Value you want to set
            ");

        AlterColumn("public.YourTableName", "YourColumnName", c => c.Int(nullable: false));
    }

Answer 3

Another way in EF core 6 would be to alter the migration script where the add column specifies a default value. You can then later drop this default value again.

public partial class AddOrderSource : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Add the column with a default value, then drop the default value.
        // This creates a non-nullable column without the migration failing because of existing data.

        migrationBuilder.AddColumn<int>(
            name: "OrderSource",
            table: "Orders",
            type: "int",
            nullable: false,
            defaultValue: 1); // Sample default value

        migrationBuilder.AlterColumn<int>(
            name: "OrderSource", 
            table: "Orders",
            oldDefaultValue: 1,
            defaultValue: null
        );
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "OrderSource",
            table: "Orders");
    }
}