Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity framework code first migration strategy with existing database

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.

like image 943
Adi Avatar asked Sep 21 '14 09:09

Adi


People also ask

What are the migration strategies in Entity Framework?

These strategies used to drop the entire database and recreate it, so you would lose the data and other DB objects. Entity Framework introduced a migration tool that automatically updates the database schema when your model changes without losing any existing data or other database objects.

How to do code first migrations with an existing database?

Starting Entity Framework 4.1 you can do Code First Migrations with an existing database. So first you have the database, create the model, enable migrations. The most important thing to remember that you should run Enable-Migrations before you do any changes to the schema since it should be in sync between your db and code.

What is code first approach in Entity Framework?

The Entity Framework code first approach provides us with three approaches while creating the database. It is the default option provided as an initializer class for code first approach. This option helps us create a database only if there is no existing database and so any accidental dropping of the database could be avoided via this option.

What is entity Entity Framework?

Entity Framework, at last, will take all the responsibility to generate a database for you for your POCO classes and data model and will take care of transactions, history, and migrations. With all the three approaches you have full control over updating the database and code as per need at any point in time.


1 Answers

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?

like image 65
spender Avatar answered Oct 12 '22 02:10

spender