Why does Entity Framework's EF Migrations Add-Migration step require a database connection string?

Question

I am trying to use and understand EF Migrations (using EF 4.3.1, Code First). In order to scaffold a new change, I have to use a command like this:

Add-Migration MyMigration
   -ConnectionString "Data Source=.;Initial Catalog=mydb;" 
   -ConnectionProviderName "System.Data.SqlClient"
   -StartUpProjectName MyWebsite 
   -ProjectName MyEF.Migrations

Why does Add-Migration require connection string data? Update-Database needs one, that makes sense. But doesn't Add-Migration have everything it needs from the DbContext and the Configuration?

It's not simply idle wonder, it's very confusing to give it a database, because we have a "multi-tenancy" thing where the desired database is flexible and may change from request to request, let alone at static compile time. So if Add-Migration is actually USING that database for anything, we have a problem.

UPDATE: We gave up on EF Migrations and are using Fluent Migrator instead, and are happy. It's much, much faster, even counting the fact that we have to write some things twice (once for the EF object and once for the Migration), and it doesn't have the problems discussed in this question.

Answer 1

I got curious when reading your question, so I fired up a Sql Server Profiler to have a look at what's happening when add-migration is run. It does indeed connect to the database and accesses the DB to check the __MigrationHistory table.

This is also shown by the error message produced when trying to create a second code-based migration without running the first:

Unable to generate an explicit migration because the following explicit migrations are pending: [201205291928386_foo]. Apply the pending explicit migrations before attempting to generate a new explicit migration.

I think that the migrations engine uses the serialized model from the database to calculate what migration steps should be included in the new migration.

As far as I understand, the database is only used as a helper for the code generation. As long as all the various databases you use are compatible with the model in the code this shouldn't be a problem for your.

Edit

As @Ladislav Mrnka points out, a check with the database is required if mixing code-based and automatic migrations. When you scaffold up a new migration, it should include anything that has changed in your model since the last migration. If you're using automatic migrations, those are not tracked in the code. When calculating what changes to include in the migration, the last run migration is used as a base. The only way to check for that is the database - since automatic migrations might be turned on.

If you're running with only code-based migrations (which I think is the only option to keep control), then that database can be regarded as just a code generation help. As long as the model compatibility is ensured in all databases that you connect to, everything should work.

Answer 2

Add-Migration checks existence of the database and interacts with __MigrationHistory table. As @Anders Abel mentioned it is used for investigating pending migrations and also for selecting previous model to actually find what has changed - this is especially important if you add explicit migration into solution where automatic migrations are enabled.

Answer 3

I watched this video by Rowan Miller from march of 2014: Migrations - Under the Hood

In the video Rowan explains that the Add-Migration command performs several steps which include a component called EdmModelDiffer. The EdmModelDiffer compares the current model with a previous model from the last migration (which is embedded in the resx file of the previous migration) and then calculates the required changes to the database.

So the EdmModelDiffer component needs the database connection.

The steps described in the video are:

1. Build current model from code
2. Get previous model from last migration (stored as snapshot in resx file)
3. Calculate required database changes (done by the EdmModelDiffer)
4. Generated the new migration file

Theoretically one could presume that it would be enough to compare that current model to the model of the last migration to generate the new migration. But in the meantime other people could have performed changes in the database too. That's probably why there is also a check against the database. Without doing this, the resulting migration file would not need to be correct.

Take also a look at the second video called Migrations - Team Environments

Answer 4

OP wrote:

But doesn't Add-Migration have everything it needs from the DbContext and the Configuration?

No - as others mentioned here the Designer portion of the code for a Manual Migration (what's created by add-migration) contains a snapshot of your database schema.

That said, the fact you're using a connection string etc is very odd. EF normally implies it from your DbContext class(es) and Web.Config. In a project where I have a single database and a single DbContext, I create a Configuration class and add a manual migration with:

add-migration

I don't have to pass any other command line arguments. That's in EF 4.3.1 - perhaps you were using a CTP or some older version, or just misunderstood the docs?

If I have multiple DBs or DbContexts, then I have multiple Configuration classes and use for example:

add-migration -conf Log

Which uses my Configuration class and related connection string in Web.config to add a manual migration for that database/DbContext.

Here's a longer code example of a simple DbContext meant for storing logs (separate from the main db):

namespace MyProj.Models.Log
{
    public class LogDb : DbContext
    {
        public DbSet<LogLine> LogLines { get; set; }
        public DbSet<LogTag> LogTags { get; set; }


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }
    }

    public LogDb()
#if DEPLOYDB
         : base("LogDeploy")
#else
         : base()
#endif
     {
     }
}

namespace MyProj.Migrations
{
    internal sealed class Log : DbMigrationsConfiguration<LogDb>
    {
        public Log()
        {
            AutomaticMigrationsEnabled = true;
        }
    }
}

In Web.Config:

<add name="LogDb" connectionString="Initial Catalog=Log;Data Source=.\SqlExpress;Integrated Security=SSPI;MultipleActiveResultSets=true" providerName="System.Data.SqlClient" />
<add name="LogDeploy" connectionString="Initial Catalog=Log;Data Source=00.00.000.00,12345;User ID=sql;Password=xxx;Network Library=DBMSSOCN" providerName="System.Data.SqlClient" />

So in this example, I have multiple databases, multiple DbContexts. The LogDb uses a different connection string in Web.Config based on whether "DBDEPLOY" is defined at compile time; if it is, it uses "LogDeploy." If not, it uses the default - the connection string with the same name as the class, "LogDb." This allows me to easily deploy DB changes to a server from my local machine, by switching my Project Configuration, opening a port on the SQL db machine, and running:

> update-database -conf Log

in the Package Manager Console.