locked
Migrating Boolean Not Null to Production RRS feed

  • Question

  • User438705957 posted

    I'm just about to migrate the latest version of my code-first EF deployment to Production, which is configured as MIgrateDatabaseTolatestVersion

    There are changes to a model class involving the addition of two Boolean fields which has ended up in the database as not null, with a 'Default Value or Binding' of 0

    I have a vanilla definition for these properties in the model.

     	[Display(Name = "NoDisplay", AutoGenerateField = false)]
            public Boolean First_Window_Alert_Sent { get; set; }
    
            [Display(Name = "NoDisplay", AutoGenerateField = false)]
            public Boolean Second_Window_Alert_Sent { get; set; }
    1. I am not sure why EF is setting a default value of 0 in the database when I haven't specified this?
    2. What effect will inserting the non null Boolean properties have on Production records that don't currently have those two properties i.e. will EF delete the current records because they will end up with nulls in those fields.

    Thanks

    Sunday, October 20, 2019 10:43 PM

Answers

  • User-17257777 posted

    Hi Madog,

    EF code first will automatically add the default constraint (Boolean is false) to the existing records unless you set it manually. For example:

    Now, I have a class "Audit_Measure_Assessment", it has two fields.

    public class Audit_Measure_Assessment
        {
            public int Id { get; set; }
    
            public string Name { get; set; }
        }

    I use EF code first to generate the database table and add three records:

    Then, I add the addition of two Boolean fields to the class

    public class Audit_Measure_Assessment
        {
            public int Id { get; set; }
    
            public string Name { get; set; }
    
            [Display(Name = "NoDisplay", AutoGenerateField = false)]
            public Boolean First_Window_Alert_Sent { get; set; }
    
            [Display(Name = "NoDisplay", AutoGenerateField = false)]
            public Boolean Second_Window_Alert_Sent { get; set; }
        }

    After migrating and updating the database, you can see the existing records will have the addition fields with the default value false.

    Best Regards,

    JIadong Meng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 22, 2019 10:01 AM

All replies

  • User1120430333 posted

    I doubt that EF is going to delete any records, but the right thing to do is take the production database or database table or tables in question, have them deployed to a test machine and try the migration against the test database.

    Don't you have a DBA that can set this up for you?

    Monday, October 21, 2019 4:23 AM
  • User-17257777 posted

    Hi Madog,

    The default value will be false when it is not nullable. Boolean will become bit data type in database. It takes a value of 1, 0, or NULL. 0 represents false.

    https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-ver15

    EF will not delete the records, the new added two columns will be set false as default and the original data will not change.

    Best Regards,

    Jiadong Meng

    Monday, October 21, 2019 6:02 AM
  • User438705957 posted

    Good point to setup a replica of the Production database and just change my dev context to point to it.
    I don't have ready access to DBA's, but I can set this up myself.

    I replicated the change thru SSMS against an older version of the same table without the two fields.
    i.e. added the two non-null bit fields thru the designer.
    When I attempted to save the changes, SSMS pulled up with an error message about not being able to insert null to the two new fields.

    So I added a default value = 0 constraint thru SSMS, and it worked.

    I am going to be far happier if I can set up EF someway to add the default constraint.

    I added this code in the constructor of the model class, but the default constraint was still not added.

    Public Audit_Measure_Assessment()
            {
                this.First_Window_Alert_Sent = false;
                this.Second_Window_Alert_Sent = false;
            }

     This will work for new records, but its the existing records that are concerning. I think it may fail on the initial load of the new version.

    Monday, October 21, 2019 10:06 PM
  • User-17257777 posted

    Hi Madog,

    EF code first will automatically add the default constraint (Boolean is false) to the existing records unless you set it manually. For example:

    Now, I have a class "Audit_Measure_Assessment", it has two fields.

    public class Audit_Measure_Assessment
        {
            public int Id { get; set; }
    
            public string Name { get; set; }
        }

    I use EF code first to generate the database table and add three records:

    Then, I add the addition of two Boolean fields to the class

    public class Audit_Measure_Assessment
        {
            public int Id { get; set; }
    
            public string Name { get; set; }
    
            [Display(Name = "NoDisplay", AutoGenerateField = false)]
            public Boolean First_Window_Alert_Sent { get; set; }
    
            [Display(Name = "NoDisplay", AutoGenerateField = false)]
            public Boolean Second_Window_Alert_Sent { get; set; }
        }

    After migrating and updating the database, you can see the existing records will have the addition fields with the default value false.

    Best Regards,

    JIadong Meng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 22, 2019 10:01 AM
  • User438705957 posted

    Thank you all for responding.

    The gold nugget of info was that EF will add the default value constraint to existing records.

    I am in the process of proving this, just waiting for my DBA to do a backup of Prod on production server, so I can restore a clone of Prod to development server. I tried the export data option between servers, but it didn't respect the value of identity keys

    Wednesday, October 23, 2019 2:57 AM