CODEWITHSUNDEEP
c#sql-serverentity-framework
DarthVader
DarthVader

Reputation: 55032

Entity framework migrations. Updating a boolean column to enum

I have a production database with a table which have boolean fields as 0,1

Now i need to update the set these fields to enums.

Is this possible? Do i lose data?

I can have correspondig fields to boolean.

I am on my way to airport , i havent tried anything but i m really curious if this can be done.

Upvotes: 0

Views: 5027

Answers (3)

Pawel
Pawel

Reputation: 31610

bool type in C# is not a valid underlying enum type. The smallest type you can use as the underlying type is byte. But (AFAIR) byte type maps to a tinyint and not to bit type on the Sql Server side. So first you would have to convert your bit column in the database to tinyint and then change your model to use an enum whose underlying type is byte for the property which is now bool. Finally, make sure that the enum values correspond to numbers you have in the database. By default enum values starts from 0 so you should be good without defining values explicitly.

Upvotes: 0

Augusto Barreto
Augusto Barreto

Reputation: 3695

I have just tried this myself using EF 6.1.2:

1) Added a boolean column to a class:

public class Company
    {
        public int Id { get; set; }
        [Required, StringLength(100)]
        public string Name { get; set; }
        public DbGeography Location { get; set; }
        public bool ColumnToConvert { get; set; }
    }

2) Generated the corresponding migration:

public partial class AddBooleanProperty : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.Company", "ColumnToConvert", c => c.Boolean(nullable: false, defaultValue: true));
    }

    public override void Down()
    {
        DropColumn("dbo.Company", "ColumnToConvert");
    }
}

3) Insert some rows in the Seed method:

        ctx.Companies.AddOrUpdate<Company>(
          c => c.Name,
          new Company { Name = "Company 1", ColumnToConvert = true },
          new Company { Name = "Company 2", ColumnToConvert = false },
          new Company { Name = "Company 3", ColumnToConvert = true }
        );

4) Changed the property type from bool to EnumExample:

public EnumExample ColumnToConvert { get; set; }

where EnumExample is:

public enum EnumExample
{
    False,
    True,
    OtherValue,
    AnotherValue
}

5) Generated a new migration:

    public override void Up()
    {
        Sql("ALTER TABLE dbo.Company DROP CONSTRAINT DF__Company__ColumnT__47DBAE45"); // Manually added
        AlterColumn("dbo.Company", "ColumnToConvert", c => c.Int(nullable: false, defaultValue: 2));
    }

    public override void Down()
    {
        Sql("ALTER TABLE dbo.Company DROP CONSTRAINT DF__Company__ColumnT__47DBAE45");  // Manually added
        AlterColumn("dbo.Company", "ColumnToConvert", c => c.Boolean(nullable: false, defaultValue: true));
    }

Here I realized that I had to manually drop the DF constraint because when I ran the migration it gave me an error telling me that the column was dependent on the constraint.

6) And then I modified the seed method:

ctx.Companies.AddOrUpdate<Company>(
              c => c.Name,
              new Company { Name = "Company 4", ColumnToConvert = EnumExample.AnotherValue },
              new Company { Name = "Company 5", ColumnToConvert = EnumExample.OtherValue },
              new Company { Name = "Company 6", ColumnToConvert = EnumExample.True }
            );

RESULT (before and after):

Migrating property from bool to enum

I hope this helps.

Otherwise, you can always replace the content of your migrations with a script generated by Schema Compare. SC is much more intelligent. It often creates temporal tables to migrate the data, and it handles all the "drop & create constraint" for you.

Upvotes: 4

Daniel Soares
Daniel Soares

Reputation: 21

Boolean is an implicit integer so... you can do the conversion without lose data. Of course, talking about data. If you mapped by any database framework, you´ll have to remap it correctly

Upvotes: 0

Related Questions