Entity framework code first migration strategy with existing database

Question

I have the following situation and unable to determine correct migration strategy. Help is appreciate.

• Application creates and uses database as data storage
• Application needs to update database on the start up if needed
• using Nuget Manager console is not a option. (For migration purpose , locally no problem)
• I Have existing database in distribution which are not EF

Now I want to start using the EF code first approach. What I need to achieve is :

1. If no database then create one
2. If database exists use empty migration (just to be ready for the next upgrades)
3. This should happened on application start

Database don't exists ====> Create EF Initial =====> Upg v1 =====> Upg V2

Database Exists =====> Skip Initial but be ready for next upgrades =====> Upg v1 ======> Upg v2

Thanks for your help

Additional info: This is database that exists (just an example):

CREATE DATABASE Test
GO

Use Test
GO
CREATE SCHEMA [TestSchema] AUTHORIZATION [dbo]
GO
CREATE TABLE [TestSchema].[Table1](
    [Id] [uniqueidentifier] NOT NULL,
    [Column1] [nvarchar](500) NOT NULL,
    [Column2] [bit] NOT NULL,
    [Column3] [bit] NOT NULL,
 CONSTRAINT [PK_MonitorGroups] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Using reverse engineering EF created initial migration :

public partial class Initial : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "TestSchema.Table1",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        Column1 = c.String(nullable: false, maxLength: 500),
                        Column2 = c.Boolean(nullable: false),
                        Column3 = c.Boolean(nullable: false),
                    })
                .PrimaryKey(t => t.Id);
        }

        public override void Down()
        {
            DropTable("TestSchema.Table1");
        }
    }

if I use the code provided by @spender against non existing database everything is cool . If I use it against existing database it works until i change the model (next migration).

What I have seen is that upgrade script returned by migration contains entire database creation. And can not be executed against already existing objects.

What can actually work is to add migration table to existing database and add initial data, but I am not sure that this is a good solution.

Answer 1

This took a considerable while for me to figure out, so I'm happy to share it here.

So first you'll need to reverse engineer your database. Entity framework power tools can do this for you. Once it's installed, in your project, install EF with nuget, right click the project node in solution explorer, then Entity Framework -> Reverse Engineer Code First. This will generate a whole bunch of model classes and mapping classes to your project.

Next, in Package Manager Console

Enable-Migrations

then

Add-Migration Initial

to create a migration that describes the transition from empty DB to the current schema.

Now edit the generated Configuration.cs class constructor:

    public Configuration()
    {

        AutomaticMigrationsEnabled = false;
        AutomaticMigrationDataLossAllowed = false;
    }

Next, at app startup, (so perhaps in global.asax Application_Start if you're running from a webserver), you need to trigger migrations. This method will do the job:

    public static void ApplyDatabaseMigrations()
    {
        //Configuration is the class created by Enable-Migrations
        DbMigrationsConfiguration dbMgConfig = new Configuration()
        {
            //DbContext subclass generated by EF power tools
            ContextType = typeof(MyDbContext)
        };
        using (var databaseContext = new MyDbContext())
        {
            try
            {
                var database = databaseContext.Database;
                var migrationConfiguration = dbMgConfig;
                migrationConfiguration.TargetDatabase =
                    new DbConnectionInfo(database.Connection.ConnectionString,
                                         "System.Data.SqlClient");
                var migrator = new DbMigrator(migrationConfiguration);
                migrator.Update();
            }
            catch (AutomaticDataLossException adle)
            {
                dbMgConfig.AutomaticMigrationDataLossAllowed = true;
                var mg = new DbMigrator(dbMgConfig);
                var scriptor = new MigratorScriptingDecorator(mg);
                string script = scriptor.ScriptUpdate(null, null);
                throw new Exception(adle.Message + " : " + script);
            }
        }
    }

Now you can add more migrations as normal. When the app runs, if these migrations haven't been applied, they will be applied when ApplyDatabaseMigrations is called.

Now you're right in the EF code-first fold. I think that's what you asked, right?