How to set value to an altered column based on its previous value with Entity Framework without using raw SQL?

Viewed 49

I have the following case:

I need to change the type of a column from bool to an enum:

From this:

 public class Project : BaseEntity
 {
     public bool IsAccountManagerIncludedAsApprover { get; set; }
 }

To this:

public class Project : BaseEntity
{
    public AccountManagerUserRequestOptions AccountManagerUserRequestOption { get; set; }
}

The enum AccountManagerUserRequestOptions is the following:

public enum AccountManagerUserRequestOptions
{
    ProcessUserRequest,
    OnlyNotifyForUserRequests,
    DoNothing
}

What I need to do is where the value is true (when the column is of type bool) to set AccountManagerUserRequestOptions.ProcessUserRequest.

I have found one way to do it via explicit migrations like so:

public partial class Change_IsAccountManagerIncludedAsApprover_InProjectEntity : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.Projects", "AccountManagerUserRequestOption", c => c.Int(nullable: false));

        Sql(@"UPDATE dbo.Projects SET AccountManagerUserRequestOption = 0 WHERE IsAccountManagerIncludedAsApprover = 1");
        Sql(@"UPDATE dbo.Projects SET AccountManagerUserRequestOption = 2 WHERE IsAccountManagerIncludedAsApprover = 0");
        DropColumn("dbo.Projects", "IsAccountManagerIncludedAsApprover");
    }

    public override void Down()
    {
        AddColumn("dbo.Projects", "IsAccountManagerIncludedAsApprover", c => c.Boolean(nullable: false));
        Sql(@"UPDATE dbo.Projects SET IsAccountManagerIncludedAsApprover = 1 WHERE AccountManagerUserRequestOption = 0");
        Sql(@"UPDATE dbo.Projects SET IsAccountManagerIncludedAsApprover = 0 WHERE AccountManagerUserRequestOption = 2");
        DropColumn("dbo.Projects", "AccountManagerUserRequestOption");
    }
}

The problem here is that I want to avoid the raw SQL queries

Sql(@"UPDATE dbo.Projects SET IsAccountManagerIncludedAsApprover = 1 WHERE AccountManagerUserRequestOption = 0");
Sql(@"UPDATE dbo.Projects SET IsAccountManagerIncludedAsApprover = 0 WHERE AccountManagerUserRequestOption = 2");

and

Sql(@"UPDATE dbo.Projects SET AccountManagerUserRequestOption = 0 WHERE IsAccountManagerIncludedAsApprover = 1");
Sql(@"UPDATE dbo.Projects SET AccountManagerUserRequestOption = 2 WHERE IsAccountManagerIncludedAsApprover = 0");

Is there a way to do this data migration without using raw SQL?

The reason why I ask this is because we are migrating to PostgreSQL and want to avoid raw SQL queries.

0 Answers
Related