What is the correct use of IDatabaseInitializer in EF?

Question

I have a custom DatabaseInitialiser which is below

/// <summary>
/// Implements the IDatabaseInitializer to provide a custom database initialisation for the context.
/// </summary>
/// <typeparam name="TContext">TContext is the DbContext</typeparam>
public class ParikshaDataBaseInitializer<TContext> : IDatabaseInitializer<TContext> where TContext : DbContext
{
    /// <summary>
    /// The method to Initialise the database.
    /// Takes care of the database cannot be dropped since it is in use problem while dropping and recreating the database.
    /// </summary>
    /// <param name="context">The DbContext on which to run the initialiser</param>
    public void InitializeDatabase(TContext context)
    {
        var exists = context.Database.Exists();

        try
        {
            if (exists && context.Database.CompatibleWithModel(true))
            {
                // everything is good , we are done
                return;
            }

            if (!exists)
            {
                context.Database.Create();
            }
        }
        catch (Exception)
        {
            //Something is wrong , either we could not locate the metadata or the model is not compatible.
            if (exists)
            {
                context.Database.ExecuteSqlCommand("ALTER DATABASE Pariksha SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
                context.Database.ExecuteSqlCommand("USE Master DROP DATABASE Pariksha");
                context.SaveChanges();
            }

            context.Database.Create();
        }
    } 
}

something about the above code is more than just hacky (Feel free to chip in with help)

I then added migrations and got the migration script to work correctly as well.

    internal sealed class Configuration : DbMigrationsConfiguration<ParikshaContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
            ContextKey = "EFRepository.Context.ParikshaContext";
        }

        protected override void Seed(ParikshaContext context)
        {
        }
    }

the migrations work as expected.

Now, the questions is in my application startup what should I do ? Something like this

 var config = new Configuration();
 var migrator = new DbMigrator(config);
 migrator.Update();

and some forums suggested this as well in the constructor which seems a little odd because I don't want to check if the db and schema are correct everytime I use the Context. So, what could be the possible use of this technique or did I get the context of the suggestion as wrong ?

public ParikshaContext() : base("Pariksha")
        {           
          Database.SetInitializer(new ParikshaDataBaseInitializer<ParikshaContext>());
        }

To summarise,

1. what is the correct use-case for the different techniques available ?

2. what would be the ideal strategy so that the migrations work in all conditions and when we move databases from one environment to another ?

Answer 1

This is my attempt at Db Initializer which combines both Migration initializer and the default Db Create one with seeding. (note: it's not ideal, more like a simple exercise but gives a solution to what you're asking here, mostly works - just check all the updates I made).

How to create initializer to create and migrate mysql database?

As for the why and how - to fully understand that I'd suggest you also consult the EF source code (that's new version but in many ways similar)

1)

a) Db initializer gets called usually only once (per connection) - and when you try to access your 'model' for the first time (first query or similar). Put a breakpoint in your initializer to check.

So it's completely safe to put it within constructor (though I prefer it on startup somewhere, config also). It only gets called when it's needed to initialize (and the last one set is used), you shouldn't invoke it manually.

Anyhow, to force the initializer you can do the this.Database.Initialize(force: true);

For when switching connections see my post on problems
Code first custom connection string and migrations without using IDbContextFactory

b) If you create your own IDatabaseInitializer and you still want migrations to work side by side

You shouldn't just call DbMigrator from outside - as your custom initializer will miss on the whole 'db creation' (e.g. if you wish to seed or something - check my example above).

Both things are effectively 'initializers' - so you'd need to integrate them into one, that'd chain things in some way. Have in mind that order of execution is important (see above ex. for problems) - you should check for 'empty condition', then call DbMigrator, then do your own intialization. I used one initializer as a base class, and merged the other.

And if you just want to seed - you can use the migration Configuration, that's the simplest if plausible.

2)

Is pretty 'open ended' and there is no single answer. Usually it works, but issues are expexted...

• Migrations are 3 things (as I see it) - your code model/entities, your database/tables, and the __MigrationHistory system table in the Db. All 3 need to stay in sync. If you get 'out of sync', you can drop the migration table, recreate migrations (with a flag to keep existing db) and then move on as before - i.e. there are solutions for when with live data. For this see How to ignore a table/class in EF 4.3 migrations,

• you'd need permissions to drop/create Db for when moving database,

• make sure your connection is right (change in config - and in sync with your DbContext name or ctor),

• keep it simple, don't do fancy things or switch connections from code (possible but has problems) etc.,

• don't mix database / code versions - i.e. one code entities version - one database. If you want to share the same Db with different code versions (e.g. staging, production) - don't (multi-tenant solutions will be available in EF6 - e.g. this),

• if you need to manually apply the database - generate the script via Update-Database - and apply that, don't do things manually or you'll get it wrong (the migration history table) - see this one,

...that's just to name the few. It is pretty stable and usable IMO - but if you follow the rules - and know what the limitations are.

---

class CreateAndMigrateDatabaseInitializer<TContext, TConfiguration> 
    : CreateDatabaseIfNotExists<TContext>, IDatabaseInitializer<TContext>
    where TContext : DbContext
    where TConfiguration : DbMigrationsConfiguration<TContext>, new()
{
    private readonly DbMigrationsConfiguration _configuration;
    public CreateAndMigrateDatabaseInitializer()
    {
        _configuration = new TConfiguration();
    }
    public CreateAndMigrateDatabaseInitializer(string connection)
    {
        Contract.Requires(!string.IsNullOrEmpty(connection), "connection");

        _configuration = new TConfiguration
        {
            TargetDatabase = new DbConnectionInfo(connection)
        };
    }
    void IDatabaseInitializer<TContext>.InitializeDatabase(TContext context)
    {
        var doseed = !context.Database.Exists();
        // && new DatabaseTableChecker().AnyModelTableExists(context);
        // check to see if to seed - we 'lack' the 'AnyModelTableExists'
        // ...could be copied/done otherwise if needed...

        var migrator = new DbMigrator(_configuration);
        // if (doseed || !context.Database.CompatibleWithModel(false))
        if (migrator.GetPendingMigrations().Any())
            migrator.Update();

        // move on with the 'CreateDatabaseIfNotExists' for the 'Seed'
        base.InitializeDatabase(context);
        if (doseed)
        {
            Seed(context);
            context.SaveChanges();
        }
    }
    protected override void Seed(TContext context)
    {
    }
}

Answer 2

To summarise,

1) what is the correct use-case for the different techniques available ?

2) what would be the ideal strategy so that the migrations work in all conditions 
and when we move databases from one environment to another ?

Step back and ask yourself when you would want EF to do the initializations and migrations. In my (enterprise) organization, we have very strict rules about how code, data and DDL is migrated, so these features offer us no value. Even if we could use them, I would be very wary until I had quite a lot of experience with the tool in different environments.

So, why use it? You're starting a new green field project and are using code-first. Now, here is where it is useful. You can write your code, compile, run, and let EF worry about the new or missing fields, relationships, and tables. Later, you can formalize it in your development server, then remove the initializers and migraters forever more.

As for how to set it up? It sounds like you found a way that works. I agree it seems a bit hacky, but the code is similar to my initial setup. I am using MVC.NET, so I used the following in Global.asax.cs:

 Database.SetInitializer(new DomainInitializer());

Bottom line is I think your (2) is the wrong question. It should be, "do I really want to do it this way?". It might turn out that you do, but I'd consider other more traditional options first, especially if you're in a company larger than 10 people.