Migrating update databases based on the code first mode of an existing database

This article discusses content based on the EF4.1 version. The existing databases discussed in this article are not created by EF. This article assumes that you have a certain understanding of code first migration, and if you do not understand the Code first migration Update database, you can view

The article covers the following topics:

1. Create a model

2. Mobility

3. Add an initial migration

A. Use the existing schema as a starting point

B. Take an empty database as a starting point

4. Note:

A, the default/computed name may not match the existing schema

b, not all database objects are shown in model

First, create the model

The first step is to create a code model that targets the existing database.

Note: In this topic, it is important to follow the rest of the steps before making any changes to the model, and your model needs to modify the database schema. The following steps require synchronization of the model with the database schema.

Second, the mobility

The next step is to make the data migrated. You can do this by running the enable-migrations command in the NuGet Package Manager console.

This command will create a folder named Migrations in your solution and create a class named configuration in the folder. The configuration class is used to configure data migration for an application.

Third, add an initial migration

Once you have created a database migration and applied to a local database, you may also want to apply these changes to other databases. For example, your local database might be a test database, or you might end up applying those changes to a production database or other developer test database. This step has two options and you should choose whether the schema of the other database is currently empty or matches the pattern of the local database.

method One: use the existing schema as the starting point (or as the start)

You should use this method when other database migrations will be applied to a local database that has the same schema. For example, if you currently have a local test database that matches V1 's production database, you will apply these migrations to update the production database to V2.

mode two: with an empty database as starting point (or as beginning)

You should choose this method when the database migration is applied to an empty database (or a nonexistent database). For example, if you use a test database to develop your application, instead of using a database migration, create a production database from scratch.

Four, two ways of concrete operation

Way One:

Code first Database Migration stores the most recent changes made to the model through the snapshot of the model. Because we assume that the database is already a pattern of the current model, we will generate an empty (operation) migration with the current model as a snapshot.

1. Run the add-migration initialcreate-ignorechanges command in the Package Manager console. This command creates an empty migration with the current model as a snapshot.

2. Run the update-database command in the Package Manager console. This command will apply the initial migration of the creation to the database. If the actual migration does not contain any changes, it is simple to add a record to the __migrationshistory table to indicate that the migration has been applied.

Way two:

In this way, we need to use migration to create the entire database from scratch-including tables that already exist on the local database. We will generate an initial migration containing this logic and create it with an existing schema. The migration is then applied to our existing database.

1. Run the add-migration initialcreate command in the Package Manager console. This command creates a migration in the existing schema.

2. Comment out all the code for the up method in the newly created migration. This allows us to apply the resulting migration to the local database and EF will not create all the tables that already exist.

3. Run the update-database command in the Package Manager console. This applies the initialcreate migration in the database. Because the migration does not actually contain any changes, it is simple to add a record to the __migrationshistory table to indicate that the migration has been applied.

4. Cancel the commented out code in the UP method. This means that when the migration is applied to a later database, schemas that already exist in the local database are applied through migration.

Five, matters needing attention

1. The default/predicted column or table name matches the existing database

Migrating the tables and columns created for the migration explicitly specifies a name. However, these specified default names are applied to other objects in the database when this migration is used. Indexes and foreign KEY constraints are also included in the migration. When targeting an existing schema, these default names may not match the actual database that exists.

Note the following points:

A, if you choose the way one

If your model changes in the future, you need to change or delete the database object that is different from the other naming, and you need to modify the scaffold migration program to specify the correct table or column name. There are overloaded methods in the migrations APIs that can be modified by modifying optional parameters. For example, your existing database might have a post table with a BLOGID foreign key column named Indexfk_blogid. However, if you use the default name in the migration, it is renamed to Ix_blogid. If you modify the model will cause the deletion of this index, you need to modify the scaffolding dropindex to specify the index named Indexfk_blogid.

b, if you choose the way two

(1) Attempting to perform the down method in the initial migration for your local database may fail because the migration program will attempt to remove the correct index and foreign key. This only affects your local database or table and other databases or tables will be created from scratch using the up method in the initial migration.

If you want to downgrade your existing database to an empty state, by hand implementation is the simplest way, you can manually delete the database or all the database tables. All database objects are then recreated and named as the default name, so the problem does not occur.

(2) If your model changes in the future and you need to change or delete the database object that is different from the other naming, the program for your local database will not work correctly because the database object name does not match the default name. However, a program for a database created from scratch can work because the name used by the database object is the default name in the migration.

You can also manually make these changes in the local database, or consider using migration to create your database from scratch.

(3) A database created using the up method in the initial migration may be significantly different from your local database, because names with index and FOREIGN key constraints as default names are used. You can also get an extra index as the migration will create an index with the default foreign key column--the database created may not be your original local database.

2. Not all database objects are present in model

database objects that are not represented in the model are not processed by migrations. These database objects that are not represented in the model include views, stored procedures, permission permissions, tables, indexes, and so on.

Note the following points:

A, whether you choose mode one or mode two, if you modify the model later you need to modify or delete these additional objects, migrations will not know what changes have occurred. For example, if you delete a column from an extra object, migrations will not know what you are deleting. If you want migrations to know what changes have occurred, you will need to manually add the deleted columns to the scaffolding migration.

b, if you choose Mode Two, these additional objects will not be created by the initial migration's up method.

If you want the up and down methods to listen for these additional objects, you can modify the up and down methods. For objects, which are not initially supported in the Migrations API, such as views, you can use the Dbmigration.sql method to execute SQL to create or delete these objects.

Migrating update databases based on the code first mode of an existing database