EF6 DataMigration: from entry to advanced, ef6datamigration

EF6 DataMigration: from entry to advanced, ef6datamigration
Introduction

During the development of EntityFramework, the table structure is often changed due to changes in requirements or data structure design. However, when the database Schema changes, EF requires usDataMigrationAndUpdateDatabase. However, in this process, how can we ensure the existence of existing database data.

In addition, this article only applies to CodeFirst.

 

Prepare a simple EFCodeFirst Demo

 

• Create a console ProgramMigrationsDemo
• NuGet gets the latest EntityFramework
  • Tools> Library Package Manager> Package Manager Console
  • RunInstall-Package EntityFrameworkCommand

Using System. data. entity; using System. collections. generic; using System. componentModel. dataAnnotations; using System. data. entity. infrastructure; namespace MigrationsDemo {public class BlogContext: DbContext {public DbSet <Blog> Blogs {get; set ;}} public class Blog {public int BlogId {get; set ;} public string Name {get; set ;}}}

  
The Program. cs file demonstrates how to use the defined business class and DataContext.

using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace MigrationsDemo{    class Program    {        static void Main(string[] args)        {            using (var db = new BlogContext())            {                db.Blogs.Add(new Blog { Name = "Another Blog " });                db.SaveChanges();                foreach (var blog in db.Blogs)                {                    Console.WriteLine(blog.Name);                }            }            Console.WriteLine("Press any key to exit...");            Console.ReadKey();        }    }}

We can't wait to run our small program:

Public string Url {get; set ;}

Run our program again!

Ops... Something went wrong !!

Namespace MigrationsDemo. migrations {using System; using System. data. entity. migrations; public partial class AddBlogUrl: DbMigration {public override void Up () {AddColumn ("dbo. blogs "," Url ", c => c. string ();} public override void Down () {DropColumn ("dbo. blogs "," Url ");}}}

 

We can edit this migration, but we do not need to make any changes here. This is the useUpdate-DatabaseApply the Migration to the database.

• Run on the Package Manager ConsoleUpdate-DatabaseCommand
• Code First MigrationsMigrationsThe Migration in the folder has been applied to the database for comparison. In the end, we will seeAddBlogUrlWill be applied to the database.

FinalMigrationsDemo. BlogContextIn the databaseBlogsThe table is added.UrlColumn.

 

Custom Migration

At the current position, we have generated and executed a Migration without any modifications. Now we can see how to implement custom Migration by modifying the automatically generated code.

• IsBlogAddRatingAttribute

public int Rating { get; set; }

We add a Post class.

public class Post{    public int PostId { get; set; }    [MaxLength(200)]    public string Title { get; set; }    public string Content { get; set; }    public int BlogId { get; set; }    public Blog Blog { get; set; }}

 

• IsBlogAddPostsCollection attribute to organizeBlog andPost relationship

public virtual List<Post> Posts { get; set; }

 

We will useAdd-MigrationGenerate migration and name itAddPostClass.

• Run on the Package Manager ConsoleAdd-Migration AddPostClass.

Code First Migrations did a good job in organizing changes, but I still want to make some modifications:

namespace MigrationsDemo.Migrations{    using System;    using System.Data.Entity.Migrations;        public partial class AddPostClass : DbMigration    {        public override void Up()        {            CreateTable(                "dbo.Posts",                c => new                    {                        PostId = c.Int(nullable: false, identity: true),                        Title = c.String(maxLength: 200),                        Content = c.String(),                        BlogId = c.Int(nullable: false),                    })                .PrimaryKey(t => t.PostId)                .ForeignKey("dbo.Blogs", t => t.BlogId, cascadeDelete: true)                .Index(t => t.BlogId)                .Index(p => p.Title, unique: true);            AddColumn("dbo.Blogs", "Rating", c => c.Int(nullable: false, defaultValue: 3));        }                public override void Down()        {            DropIndex("dbo.Posts", new[] { "Title" });            DropIndex("dbo.Posts", new[] { "BlogId" });            DropForeignKey("dbo.Posts", "BlogId", "dbo.Blogs");            DropColumn("dbo.Blogs", "Rating");            DropTable("dbo.Posts");        }    }}

 

We modify migration to meet our needs and runUpdate-DatabaseTo upgrade the database to the latest version.-VerboseCheck the SQL Execution status.

• Run on the Package Manager ConsoleUpdate-Database-Verbose.

 

 

Data Migration during the Migration process | custom SQL

So far, we have learned that the Migration operation will not modify data or transfer data to the new database. Now let's take a look at how to handle our data. There is no native support here, however, we can implement our ideas by executing any desired SQL script at the specified position.

AddPost. AbstractAttribute. We will useContentTo generateAbstract.

public string Abstract { get; set; }

We will useAdd-MigrationCommand to let Code First Migrations generate the closest for us to the migration we want.

• Run on the Package Manager ConsoleAdd-Migration AddPostAbstractCommand.
• The generated migration notices schema changes, but it cannot be filled with the first 100 characters of content in Post.AbstractColumn. I can run it through SQLUPDATECommand.AbstractColumn.
  (Adding in line 12 in the code below)

namespace MigrationsDemo.Migrations{    using System;    using System.Data.Entity.Migrations;        public partial class AddPostAbstract : DbMigration    {        public override void Up()        {            AddColumn("dbo.Posts", "Abstract", c => c.String());            Sql("UPDATE dbo.Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL");        }                public override void Down()        {            DropColumn("dbo.Posts", "Abstract");        }    }}

We modify migration to meet our needs and runUpdate-DatabaseTo upgrade the database to the latest version.-VerboseCheck the SQL Execution status.

• Run on the Package Manager ConsoleUpdate-Database-Verbose.

 

 

Update the database to the specified version Migration (including upgrade, downgrade, or rollback)

So far, we can update the database to the latest Migration version, but sometimes we need to upgrade or downgrade to the specified Migration.

Now there is a special case to let the database go back to runningAddBlogUrlMigration status. This can be used.-TargetMigrationTo downgrade the database to this version.

• Run on the Package Manager ConsoleUpdate-Database-TargetMigration: AddBlogUrlCommand.

This command will runAddBlogAbstractAndAddPostClassThe Down script of migration.

If you want to return to the originalUpdate-Database-TargetMigration: $ InitialDatabaseThis command will help you in one step.

 

 

Obtain the Migration SQL script

If another developer wants to apply our changes to the database, he can use Source Control to obtain the latest code I checked in. Once he has the latest code, he can useUpdate-DatabaseApply all modifications to the local device. However, sometimes we want to release our modifications to the test server or even the production environment, so we may need an SQL script that can be handed over to the DBA.

• RunUpdate-DatabaseSpecify-ScriptIn this way, only scripts are generated instead of Migration. We can also specify the Source and Target of the Migration. Eg. From an empty database ($ InitialDatabase) To the latest version (migrationAddPostAbstract).
  If you don't specify a target migration, Migrations will use the latest migration as the target. if you don't specify a source migrations, Migrations will use the current state of the database. (if no Target version is specified, the system sets the Target to the latest Migration version by default. If no Source is specified, the current database status is set to Source by default)
• In the Package Manager Console, Update-Database-Script-SourceMigration: $ InitialDatabase-TargetMigration: AddPostAbstract

 

Code First Migrations will generate a. SQL file instead of applying the changes to the database. Once the SQL script is generated, Visual Studio automatically opens the script. You can choose whether to save it.

Generating Idempotent Scripts (EF6 onwards)

  
Starting with EF6, if you specify –SourceMigration $InitialDatabase then the generated script will be ‘idempotent’. Idempotent scripts can upgrade a database currently at any version to the latest version (or the specified version if you use –TargetMigration). The generated script includes logic to check the __MigrationsHistory table and only apply changes that haven't been previously applied.

 

The latest Database Import version is automatically upgraded when the application starts.

After you deploy a program, you can registerMigrateDatabaseToLatestVersionDatabase initializer to achieve This goal. It is a simple database initializer but can ensure that the database is correctly upgraded to the latest version. This logic is run the first time the context is used within the application process (AppDomain).

ModifyProgram. csFor exampleBlogContext setting MigrateDatabaseToLatestVersionInitializer. Note the need to introduceSystem. Data. EntityNamespace

  
When we create an instance of this initializer we need to specify the context type (BlogContext) and the migrations configuration (Configuration) - the migrations configuration is the class that got added to our Migrations folder when we enabled Migrations.

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.Entity;using MigrationsDemo.Migrations;namespace MigrationsDemo{    class Program    {        static void Main(string[] args)        {            Database.SetInitializer(new MigrateDatabaseToLatestVersion<BlogContext, Configuration>());            using (var db = new BlogContext())            {                db.Blogs.Add(new Blog { Name = "Another Blog " });                db.SaveChanges();                foreach (var blog in db.Blogs)                {                    Console.WriteLine(blog.Name);                }            }            Console.WriteLine("Press any key to exit...");            Console.ReadKey();        }    }}

Now our program can automatically upgrade the database to the latest version at startup.

 

Download MigrationsDemo.rar from source code

 

Reference

Code First Migrations

Code First Migrations in Team Environments

Benefits of Code First in Entity Framework 6