使用Entity Framework Core Migration命令时无法附加数据库文件(Cannot attach database file when using Entity Framework Core Migration commands)

使用Entity Framework Core Migration命令时无法附加数据库文件(Cannot attach database file when using Entity Framework Core Migration commands)

我正在使用EntityFramework Core命令迁移数据库。 我使用的命令就像文档建议的那样：dnx。 如果适用迁移。 问题是在连接字符串中指定AttachDbFileame时，出现以下错误：无法将数据库文件作为数据库xxxxxxx附加。 这是我正在使用的连接字符串：Data Source =（LocalDB）\ mssqllocaldb; Integrated Security = True; Initial Catalog = EfGetStarted2; AttachDbFileame = D：\

请帮助如何将数据库文件附加到其他位置。 谢谢

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 AttachDbFileame 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;AttachDbFileame=D:\

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

最满意答案

EF核心似乎有与AttachDbFileame麻烦或根本不处理它。

EnsureDeleted将数据库名称更改为主数据库，但保留任何AttachDbFileame值，这会导致错误，因为我们无法将主数据库附加到其他文件。 EnsureCreated使用提供的AttachDbFileame值打开连接，这会导致错误，因为我们要创建的数据库文件尚不存在。

EF6有一些逻辑来处理这些用例，请参阅SqlProviderServices.DbCreateDatabase ，所以一切工作都很好。

作为一种解决方法，我编写了一些hacky代码来处理这些情况：

public static void EnsureDatabase(this DbContext context, bool reset = false) { if (context == null) throw new ArgumentullException(nameof(context)); if (reset) { try { context.Database.EnsureDeleted(); } catch (SqlException ex) when (ex.umber == 1801) { // HACK: EF doesn't interpret error 1801 as already existing database ExecuteStatement(context, BuildDropStatement); } catch (SqlException ex) when (ex.umber == 182) { // nothing to do here (see below) } } try { context.Database.EnsureCreated(); } catch (SqlException ex) when (ex.umber == 182) { // HACK: EF doesn't interpret error 182 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.Utcow + TimeSpan.FromMinutes(1); while (true) { try { context.Database.OpenConnection(); context.Database.CloseConnection(); } catch (SqlException) { if (DateTime.Utcow > 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.ExecuteonQuery(); } } } 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.GetFileameWithoutExtension(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}')"; }

它远非不错，但无论如何我都在使用它进行集成测试。 对于使用EF迁移的“现实世界”情况应该是一条路，但也许这个问题的根本原因是一样的......

更新

下一个版本将包含对AttachDBFilename的支持 。

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

EnsureDeleted changes the database name to master but keeps any AttachDbFileame value, which leads to an error since we cannot attach the master database to another file. EnsureCreated opens a connection using the provided AttachDbFileame 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 ArgumentullException(nameof(context)); if (reset) { try { context.Database.EnsureDeleted(); } catch (SqlException ex) when (ex.umber == 1801) { // HACK: EF doesn't interpret error 1801 as already existing database ExecuteStatement(context, BuildDropStatement); } catch (SqlException ex) when (ex.umber == 182) { // nothing to do here (see below) } } try { context.Database.EnsureCreated(); } catch (SqlException ex) when (ex.umber == 182) { // HACK: EF doesn't interpret error 182 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.Utcow + TimeSpan.FromMinutes(1); while (true) { try { context.Database.OpenConnection(); context.Database.CloseConnection(); } catch (SqlException) { if (DateTime.Utcow > 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.ExecuteonQuery(); } } } 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.GetFileameWithoutExtension(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 migrati 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.