Cannot attach database file when using Entity Framework Core Migration commands

entity-framework-core

Question

I am using EntityFramework Core commands to migration database. The command I am using is like the docs suggests: dnx . ef migration apply. The problem is when specifying AttachDbFileName in connection string, the following error appear: Unable to Attach database file as database xxxxxxx. This is the connection string I am using: Data Source=(LocalDB)\mssqllocaldb;Integrated Security=True;Initial Catalog=EfGetStarted2;AttachDbFileName=D:\EfGetStarted2.mdf

Please help how to attach the db file to another location. Thanks

Accepted Answer

EF core seem to have troubles with AttachDbFileName or doesn't handle it at all.

  • EnsureDeleted changes the database name to master but keeps any AttachDbFileName value, which leads to an error since we cannot attach the master database to another file.
  • EnsureCreated opens a connection using the provided AttachDbFileName value, which leads to an error since the file of the database we want to create does not yet exist.

EF6 has some logic to handle these use cases, see SqlProviderServices.DbCreateDatabase, so everything worked quite fine.

As a workaround I wrote some hacky code to handle these scenarios:

public static void EnsureDatabase(this DbContext context, bool reset = false)
{
    if (context == null)
        throw new ArgumentNullException(nameof(context));

    if (reset)
    {
        try
        {
            context.Database.EnsureDeleted();
        }
        catch (SqlException ex) when (ex.Number == 1801)
        {
            // HACK: EF doesn't interpret error 1801 as already existing database
            ExecuteStatement(context, BuildDropStatement);
        }
        catch (SqlException ex) when (ex.Number == 1832)
        {
            // nothing to do here (see below)
        }
    }

    try
    {
        context.Database.EnsureCreated();
    }
    catch (SqlException ex) when (ex.Number == 1832)
    {
        // HACK: EF doesn't interpret error 1832 as non existing database
        ExecuteStatement(context, BuildCreateStatement);

        // this takes some time (?)
        WaitDatabaseCreated(context);

        // re-ensure create for tables and stuff
        context.Database.EnsureCreated();
    }
}

private static void WaitDatabaseCreated(DbContext context)
{
    var timeout = DateTime.UtcNow + TimeSpan.FromMinutes(1);

    while (true)
    {
        try
        {
            context.Database.OpenConnection();
            context.Database.CloseConnection();
        }
        catch (SqlException)
        {
            if (DateTime.UtcNow > timeout)
                throw;
            continue;
        }
        break;
    }
}

private static void ExecuteStatement(DbContext context, Func<SqlConnectionStringBuilder, string> statement)
{
    var builder = new SqlConnectionStringBuilder(context.Database.GetDbConnection().ConnectionString);

    using (var connection = new SqlConnection($"Data Source={builder.DataSource}"))
    {
        connection.Open();

        using (var command = connection.CreateCommand())
        {
            command.CommandText = statement(builder);
            command.ExecuteNonQuery();
        }
    }
}

private static string BuildDropStatement(SqlConnectionStringBuilder builder)
{
    var database = builder.InitialCatalog;

    return $"drop database [{database}]";
}

private static string BuildCreateStatement(SqlConnectionStringBuilder builder)
{
    var database = builder.InitialCatalog;

    var datafile = builder.AttachDBFilename;
    var dataname = Path.GetFileNameWithoutExtension(datafile);

    var logfile = Path.ChangeExtension(datafile, ".ldf");
    var logname = dataname + "_log";

    return $"create database [{database}] on primary (name = '{dataname}', filename = '{datafile}') log on (name = '{logname}', filename = '{logfile}')";
}

It's far from nice, but I'm using it for integration testing anyway. For "real world" scenarios using EF migrations should be the way to go, but maybe the root cause of this issue is the same...

Update

The next version will include support for AttachDBFilename.


Popular Answer

There may be a different *.mdf file already attached to a database named EfGetStarted2... Try dropping/detaching that database then try again.

You might also be running into problems if the user LocalDB is running as doesn't have correct permissions to the path.



Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why