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.

Solution

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?

Comments

[Adi]

@Farhad no , I just need little help :)

[Adi]

first thank you for answering. - This works nice when no database present - If no changes are present in the model database working fine (Context can be initialized) - But making changes in model will not update existing database I will edit my question to provide more info

[spender]

When you change your model, you need to Add-Migration again. This will create s new migration that gets applied on top on the previous migrations

[Adi]

let assume that all other cases work except this : EF created database with 2 migrations Initial and V1 apply against existing database. Existing database is identical to Initial and needs only V1 to be applied, but migration does not detect this because existing db doesn't have migration table (It is not created from EF).

[spender]

@Adrijan : Ok. Understood. It looks like your first migration should be generated with the following command Add-Migration Initial -IgnoreChanges. Now the first migration should not contain anything in the Up method... We just assume that the schema of the existing DB matches our model (it should do because we reverse engineered it from the existing schema). Now when you run the app, if no _MigrationHistory exists, it will be created when migrations execute. See here for more info.

[arni]

Actually this is all the code you need: var migrationConfiguration = new Configuration(); var migrator = new DbMigrator(migrationConfiguration); migrator.Update();