Update existing database with Entity Framework Code First in MVC

Question

In my MVC application I used Entity Framework 6 and created database with code first approach. After a certain time, I updated one of the entity classes by adding new column and removing some columns. For reflecting these changes to the database I followed the steps below:

1. Deleted the migrations folder in the project.
2. Deleted the __MigrationHistory table in the database.

3. Then run the following command in the Package Manager Console:
   Enable-Migrations -EnableAutomaticMigrations -Force

4. Add the following lines in configuration file:
   AutomaticMigrationsEnabled = true;
   AutomaticMigrationDataLossAllowed = true;

5. Run:
   Add-Migration Initial

6. And finally, run:
   Update-Database -Verbose

However, I encounter an error "There is already an object named 'xxx' in the database."

To get rid of this problem, I comment the code in the Up method in the initial file created after 5th step. This prevent the error but nothing is changed in the database (the updated entity tables remains as before). Where is the mistake? Thanks in advance for your help.

Here is the Up method that I commented in the migration.cs file:

    public override void Up()
    {
        CreateTable(
            "dbo.City",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    Name = c.String(nullable: false),
                    RegionID = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.ID)
            .ForeignKey("dbo.Region", t => t.RegionID)
            .Index(t => t.RegionID);

        CreateTable(
            "dbo.Multiplier",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    Status = c.Int(nullable: false),
                    Term = c.Int(nullable: false),
                    CityID = c.Int(nullable: false),
                    WhoIsOnline = c.String(nullable: false),
                    UserId = c.String(nullable: false),
                    InstituteName = c.String(nullable: false),
                    InstituteStatusID = c.Int(nullable: false),
                    InstituteAccreditationDate = c.DateTime(nullable: false),
                    Address = c.String(nullable: false),
                    Phone = c.String(nullable: false),
                    Fax = c.String(),
                    Email = c.String(nullable: false),
                    EurodeskEmail = c.String(nullable: false),
                    WebSite = c.String(),
                    ContactName = c.String(nullable: false),
                    ContactSurname = c.String(nullable: false),
                    ContactJobTitle = c.String(),
                    ContactAssignmentDate = c.DateTime(),
                    ContactWorkingStart = c.String(),
                    ContactWorkingkEnd = c.String(),
                    ContactPhone = c.String(),
                    ContactMobile = c.String(nullable: false),
                    ContactEmail = c.String(nullable: false),
                    ContactCityID = c.Int(nullable: false),
                    LegalRepresentativeName = c.String(nullable: false),
                    LegalRepresentativeSurname = c.String(nullable: false),
                })
            .PrimaryKey(t => t.ID)
            .ForeignKey("dbo.City", t => t.CityID)
            .ForeignKey("dbo.InstituteStatus", t => t.InstituteStatusID)
            .Index(t => t.CityID)
            .Index(t => t.InstituteStatusID);

        CreateTable(
            "dbo.InstituteStatus",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    Name = c.String(nullable: false),
                })
            .PrimaryKey(t => t.ID);

        CreateTable(
            "dbo.TrainingParticipant",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    TrainingID = c.Int(nullable: false),
                    ParticipantID = c.Int(nullable: false),
                    Multiplier_ID = c.Int(),
                })
            .PrimaryKey(t => t.ID)
            .ForeignKey("dbo.Participant", t => t.ParticipantID)
            .ForeignKey("dbo.Training", t => t.TrainingID)
            .ForeignKey("dbo.Multiplier", t => t.Multiplier_ID)
            .Index(t => t.TrainingID)
            .Index(t => t.ParticipantID)
            .Index(t => t.Multiplier_ID);

        CreateTable(
            "dbo.Participant",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    Name = c.String(nullable: false),
                    Surname = c.String(nullable: false),
                    MultiplierID = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.ID)
            .ForeignKey("dbo.Multiplier", t => t.MultiplierID)
            .Index(t => t.MultiplierID);

        CreateTable(
            "dbo.Training",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    Name = c.String(nullable: false),
                    Date = c.DateTime(nullable: false),
                    CityID = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.ID)
            .ForeignKey("dbo.City", t => t.CityID)
            .Index(t => t.CityID);

        CreateTable(
            "dbo.Region",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    Name = c.String(nullable: false),
                })
            .PrimaryKey(t => t.ID);

    }

And this is the Down method in the migration.cs file:

    public override void Down()
    {
        DropForeignKey("dbo.City", "RegionID", "dbo.Region");
        DropForeignKey("dbo.TrainingParticipant", "Multiplier_ID", "dbo.Multiplier");
        DropForeignKey("dbo.TrainingParticipant", "TrainingID", "dbo.Training");
        DropForeignKey("dbo.Training", "CityID", "dbo.City");
        DropForeignKey("dbo.TrainingParticipant", "ParticipantID", "dbo.Participant");
        DropForeignKey("dbo.Participant", "MultiplierID", "dbo.Multiplier");
        DropForeignKey("dbo.Multiplier", "InstituteStatusID", "dbo.InstituteStatus");
        DropForeignKey("dbo.Multiplier", "CityID", "dbo.City");
        DropIndex("dbo.Training", new[] { "CityID" });
        DropIndex("dbo.Participant", new[] { "MultiplierID" });
        DropIndex("dbo.TrainingParticipant", new[] { "Multiplier_ID" });
        DropIndex("dbo.TrainingParticipant", new[] { "ParticipantID" });
        DropIndex("dbo.TrainingParticipant", new[] { "TrainingID" });
        DropIndex("dbo.Multiplier", new[] { "InstituteStatusID" });
        DropIndex("dbo.Multiplier", new[] { "CityID" });
        DropIndex("dbo.City", new[] { "RegionID" });
        DropTable("dbo.Region");
        DropTable("dbo.Training");
        DropTable("dbo.Participant");
        DropTable("dbo.TrainingParticipant");
        DropTable("dbo.InstituteStatus");
        DropTable("dbo.Multiplier");
        DropTable("dbo.City");
    }

Condescendence

This helped me because my Oracle database seems to Drop the __MigrationHistory when the updates commands fail. I get an exception about not being able to handle the OracleException, so I am having to reset a lot. (I still don't know why Oracle isn't handling the exceptions) – Weisman

Answer 1

Why did you do steps 1-4? That's where you went wrong. If you had a previously generated database and you're just making changes to the schema, then just generate a migration and apply it. By doing steps 1-4, you're effectively undoing Entity Framework's knowledge of this database and essentially ending up with code-first with an existing database. At which point, you either have to manually change your schema or let Entity Framework blow it out and start over.

As far as getting back to a state where you can apply migrations again goes, you were on the right track with generating a migration and just emptying out the Up method. However, you need to do this against your application's previous state, i.e. the one that matches the database as it currently is. Otherwise, Entity Framework is going to generate create tables that include your code changes. So the steps to follow are:

1. Revert your code to the point before you started modifying your POCOs.
2. Generate a migration.
3. Remove everything in the Up method
4. Apply the migration with update-database
5. Re-apply the changes you made to your POCOs.
6. Generate another migration (this one should now just have add/alter column statements instead of create tables)
7. Apply the migration.

After that, you should be good to go again. Then, the next time you make code changes, just follow steps 5 & 6.

Jezabella

That is exactly what I have look for :) Thanks a lot for your answer that will help those who really need to apply in order to obtain the benefit of EF code first. Regards... – Condescendence

Thanks. This helped me a lot in ASP.NET Core 2. I am curious though. I started with database first and then did what Clint Eastwood did. Should I have stuck with the database first and created a column in the db and did a sync with my scaffolding somehow (I forgot the command)? – Tradition

Database first is a valid approach, BUT I would say only if you have a DBA on staff who's going to manage that. Unless you're a database expert, you're better off letting EF take care of the fine details, since the SQL it generates is reviewed, tweaked and monitored by database professionals, both at Microsoft and in the community. – Jezabella

Answer 2

I've had this exact problem. Seems It is worth noting that there are commands in place to aid in this situation, namely the -IgnoreChanges and -Force flags.

I was trimming from a multi-dbContext to single dbContext app. As you can guess the tables already existed, but the single context new nothing of the tables that were maintained in the second context.

It is actually quite simple (albeit 2 days of searching for the answer to no avail led me to read up on the command lines of EF Code First Migrations and the package manager...) Here is how I handled it.

You could delete migrations folder and _Migrations Table in SQL… this would then cause you to need to use the following: Enable-Migrations -Force

But you should be able to pick up from here without taking drastic measures:

1. Add-migration “Reset” –IgnoreChanges –Force (Forcibly ignores changes that may exists in your model/class – good for getting started with existing database)
2. Update-Database (just writes migration line as a basis)
3. Add-migration “AddMyMigrationsToThisDb” –Force (forcibly iterates object model in code to pick-up changes)
4. Update-Database

Now you should be back on track for just using Add-Migration and Update-Database without the extra flags.

Ophthalmologist

Thanks John for reply. – Condescendence