Without losing data, update the database schema as the model changes.

c# database-update ef-code-first entity-framework

Question

Background: If I'm using the entity framework, for example, and one of the entities that is mapped is provided as follows:

class Foo
{
    public int ID { get; set; }
    public string Bar { get; set; }
    // other useful properties
}

The mapping entails:

class FooMap : EntityTypeConfiguration<Foo>
{
    // bear me with, I know this is redundant right now
    ToTable("Foo");
    HasKey(e => e.ID);
    Property(e => e.ID).HasColumnName("ID");
    Property(e => e.Bar);
}

The column name of needs to be changed at some point in the future.Foo when my supervisor informs me thatFoo should have a more creative name thanID else, our clients will be dissatisfied. He instructs us to rename it "FooID" as well.

class FooMap : EntityTypeConfiguration<Foo>
{
    // bear me with, I know this is redundant right now
    ToTable("Foo");
    HasKey(e => e.ID);
    Property(e => e.ID).HasColumnName("FooID"); // Now you map to FooID
    Property(e => e.Bar);
}

As it is, nothing will have changed if I run this. TheFoo still have a column with the nameID and an exception will be raised informing me that the columnFooID is not true.

How can I get the entity framework to understand that I want to update theFoo to a table such that the identifiedID now has a nameFooID ?

In a broader sense, how can I enable the entity framework to automatically propagate changes from my code into the real database (say, at the start up of the program) WITHOUT erasing current data?

For reasons beyond the purview of this inquiry, the database maintains some additional data that wasn't produced using the models I mapped in my code, so I can't just "Drop and re-create" it. If we dropped it, we'd lose tables that we couldn't easily replicate using this approach.

1
3
11/19/2018 4:10:47 PM

Accepted Answer

5
11/30/2016 6:17:07 AM

Popular Answer

How to get out of the nightmarish scenario when the same-name tables exist in the database because of Entity Framework

Description: If your team is new to EF, you'll wind yourself in a situation similar to ours where you can't either build a new local database or update your production database. You want to return to a pure EF environment and then adhere to the fundamentals, but you are unable to do so. You cannot establish a local database if you get it working for production, and if you get it working for local, your production server becomes out of sync. Finally, you don't want to remove any data from the production server.

Zzz-14-Zzz: Zzz-19-Zzz cannot be executed because it is attempting to execute the creation script when the database already contains tables with the same name.

Error Message: System.Data.SqlClient.SqlException (0x80131904): There is already an object named '' in the database.

EF uses a table in the database called dbo. MigrationHistory to determine where the current database is in relation to where the code is. It attempts to remember where it was with the Migration Scripts when it looks at them. It merely attempts to apply them in sequence if it can't if it can. This implies that it returns to the original creation script, and the CreeateTable for the table where the issue was happening may be found in the very first section of the UP command.

I suggest viewing both of the movies cited above in order to comprehend this in further detail: https://msdn.microsoft.com/en-us/library/dn481501(v=vs.113).aspx

Solution: In order to avoid using these CreateTable commands, we must mislead EF into believing that the existing database is up to date. However, we still need those commands to be available so that we may build new local databases.

Cleaning up the production database is the first step. Create a backup of your production database first. Right-click the database in SSMS, choose "Tasks > Export Data-tier application," and then follow the on-screen instructions. The dbo. MigrationHistory table should be deleted or dropped in your production database.

Second step: a clean local environment Delete it by opening your migrations folder. I'm assuming you can get anything from git if needed.

3. Recreate the initial Run "Enable-Migrations" in the package manager (EF will prompt you to use -ContextTypeName if you have multiple contexts). "Add-Migration Initial -verbose" should be run. This will generate the first script necessary to build the database from the ground up using the present code. Copy whatever seed operations you had in the prior Configuration.cs file.

Step 4: EF Trick At this point, the original mistake would appear if we run Update-Database. Therefore, we must deceive EF into believing that it is current without really performing these instructions. So, open the Initial migration you just created's Up method and comment everything out.

Update-Database (Step 5) Since there is no code to run on the Up process, EF will construct the proper item in the dbo. MigrationHistory table to indicate that the script was successfully executed. If you like, go over and have a look. Uncomment that code now, then save. If you wish to confirm that EF believes it to be up to date, you may run Update-Database once again. Because it believes that it has already completed this, it won't execute the Up step with all of the CreateTable instructions.

Step 6: Verify that EF is indeed current This is what I did if you had code that hadn't yet had migrations applied to it.

Activate "Add-Migration MissingMigrations" It will almost certainly result in an empty script. Since the code to generate these tables was already included in the original migration script, I simply incorporated the CreateTable command and its corresponding Drop command into the Up and Down procedures.

Run Update-Database once again and see as it executes your updated migration script, establishing the necessary database tables.

Reaffirm and commit in step 7. Build, check, and run. Once everything is up and running, commit the modifications.

Step 8: Explain the next steps to the rest of your team. Given that the scripts it has previously ran don't exist, EF won't know what hit it when the next person changes. However, this is all okay provided that local databases can be destroyed and recreated. They will have to remove their current local database and install a new one made using EF. I would advise them to recreate their database on master, move to their feature branch, and start again with those migration scripts if they have local modifications and outstanding migrations.



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