Update existing database with Entity Framework Code First in MVC

asp.net-mvc ef-code-first entity-framework sql-server updatemodel

Question

I utilized Entity Framework 6 and a code-first strategy to develop the database for my MVC application. After some time had passed, I modified one of the entity classes by adding a new column and deleting a few others. I took the following actions to update the database to reflect these changes:

  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

But I keep getting the error "The database already contains an object with the name 'xxx'.".

The code in the Up method of the first file made after step five is commented to solve this issue. This avoids the problem, but the database is left unchanged (the updated entity tables remains as before). What error is this? Thanks for your assistance in advance.

The Up method, which I commented in the migration.cs file, is as follows:

    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);

    }


The Down method in the migration.cs file is as follows:

    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");
    }
1
10
2/16/2015 7:59:39 PM

Accepted Answer

How come you followed steps 1-4? That is where you erred. Simply create a migration and apply it if all you're doing is making changes to the schema in an already generated database. As a result of steps 1-4, Entity Framework's knowledge of this database is basically undone, and you end up with a code-first approach with an existing database. At that point, you can either manually alter your schema or start over by letting Entity Framework destroy it.

You were on the correct track with producing a migration and simply deleting the cache to return to a position where you may apply migrations once more.Up method. You must, however, compare this to the previous state of your program, which corresponds to the present state of the database. If not, Entity Framework will produce tables with your code modifications in them. The subsequent steps are thus:

  1. Revert your code to the state it was in prior to your POCO modifications.
  2. Build a migration.
  3. Take everything out of theUp method
  4. Utilize the migration inupdate-database
  5. Apply the modifications you made to your POCOs again.
  6. Create a new migration. (Instead of construct tables, this one should now just have add/alter column statements.)
  7. Execute the migration.

You should then be ready to go once more. Then, merely adhere to steps 5 and 6 the following time you make code changes.

13
2/16/2015 8:46:20 PM

Popular Answer

I've experienced this exact issue. It seems important to note that the -IgnoreChanges and -Force flags are commands that can be used to help in this circumstance.

From a multi-dbContext to a single dbContext app, I was trimming. As you might have guessed, the tables were already in existence; however, the single context was unaware of the existence of the second context's tables.

Actually, it's very easy (albeit 2 days of searching for the answer to no avail led me to read up on the EF Code First Migrations' command lines and the package manager...) Here is what I did about it.

You might remove the migrations folder and the _Migrations table from SQL, which would need you to utilize the subsequent methods: Allow-Migrations -Force

However, you ought to be able to continue from here without taking dramatic action:

  1. oeReset, ignore changes, and force migration (Forcibly ignores changes that could already be present in your model or class; useful for starting with an existing database)
  2. Update-Database (just writes migration line as a basis)
  3. Force: Add-migration "oeAddMyMigrationsToThisDb" (forcibly iterates object model in code to pick-up changes)
  4. Update-Database

You should now be able to use Add-Migration and Update-Database without any additional flags.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow