When writing some unit tests for our application, I stumbled upon some weird behaviour in EF6 (tested with 6.1 and 6.1.2): apparently it is impossible to repeatedly create and delete databases (same name/same connection string) within the same application context.
Test setup:
public class A
{
public int Id { get; set; }
public string Name { get; set; }
}
class AMap : EntityTypeConfiguration<A>
{
public AMap()
{
HasKey(a => a.Id);
Property(a => a.Name).IsRequired().IsMaxLength().HasColumnName("Name");
Property(a => a.Id).HasColumnName("ID");
}
}
public class SomeContext : DbContext
{
public SomeContext(DbConnection connection, bool ownsConnection) : base(connection, ownsConnection)
{
}
public DbSet<A> As { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Configurations.Add(new AMap());
}
}
[TestFixture]
public class BasicTest
{
private readonly HashSet<string> m_databases = new HashSet<string>();
#region SetUp/TearDown
[TestFixtureSetUp]
public void SetUp()
{
System.Data.Entity.Database.SetInitializer(
new CreateDatabaseIfNotExists<SomeContext>());
}
[TestFixtureTearDown]
public void TearDown()
{
foreach (var database in m_databases)
{
if (!string.IsNullOrWhiteSpace(database))
DeleteDatabase(database);
}
}
#endregion
[Test]
public void RepeatedCreateDeleteSameName()
{
var dbName = Guid.NewGuid().ToString();
m_databases.Add(dbName);
for (int i = 0; i < 2; i++)
{
Assert.IsTrue(CreateDatabase(dbName), "failed to create database");
Assert.IsTrue(DeleteDatabase(dbName), "failed to delete database");
}
Console.WriteLine();
}
[Test]
public void RepeatedCreateDeleteDifferentName()
{
for (int i = 0; i < 2; i++)
{
var dbName = Guid.NewGuid().ToString();
if (m_databases.Add(dbName))
{
Assert.IsTrue(CreateDatabase(dbName), "failed to create database");
Assert.IsTrue(DeleteDatabase(dbName), "failed to delete database");
}
}
Console.WriteLine();
}
[Test]
public void RepeatedCreateDeleteReuseName()
{
var testDatabases = new HashSet<string>();
for (int i = 0; i < 3; i++)
{
var dbName = Guid.NewGuid().ToString();
if (m_databases.Add(dbName))
{
testDatabases.Add(dbName);
Assert.IsTrue(CreateDatabase(dbName), "failed to create database");
Assert.IsTrue(DeleteDatabase(dbName), "failed to delete database");
}
}
var repeatName = testDatabases.OrderBy(n => n).FirstOrDefault();
Assert.IsTrue(CreateDatabase(repeatName), "failed to create database");
Assert.IsTrue(DeleteDatabase(repeatName), "failed to delete database");
Console.WriteLine();
}
#region Helpers
private static bool CreateDatabase(string databaseName)
{
Console.Write("creating database '" + databaseName + "'...");
using (var connection = CreateConnection(CreateConnectionString(databaseName)))
{
using (var context = new SomeContext(connection, false))
{
var a = context.As.ToList(); // CompatibleWithModel must not be the first call
var result = context.Database.CompatibleWithModel(false);
Console.WriteLine(result ? "DONE" : "FAIL");
return result;
}
}
}
private static bool DeleteDatabase(string databaseName)
{
using (var connection = CreateConnection(CreateConnectionString(databaseName)))
{
if (System.Data.Entity.Database.Exists(connection))
{
Console.Write("deleting database '" + databaseName + "'...");
var result = System.Data.Entity.Database.Delete(connection);
Console.WriteLine(result ? "DONE" : "FAIL");
return result;
}
return true;
}
}
private static DbConnection CreateConnection(string connectionString)
{
return new SqlConnection(connectionString);
}
private static string CreateConnectionString(string databaseName)
{
var builder = new SqlConnectionStringBuilder
{
DataSource = "server",
InitialCatalog = databaseName,
IntegratedSecurity = false,
MultipleActiveResultSets = false,
PersistSecurityInfo = true,
UserID = "username",
Password = "password"
};
return builder.ConnectionString;
}
#endregion
}
RepeatedCreateDeleteDifferentName completes successfully, the other two fail. According to this, you cannot create a database with the same name, already used once before. When trying to create the database for the second time, the test (and application) throws a SqlException, noting a failed login. Is this a bug in Entity Framework or is this behaviour intentional (with what explanation)?
I tested this on a Ms SqlServer 2012 and Express 2014, not yet on Oracle. By the way: EF seems to have a problem with CompatibleWithModel being the very first call to the database.
Update: Submitted an issue on the EF bug tracker (link)
Database initializers only run once per context per AppDomain. So if you delete the database at some arbitrary point they aren't going to automatically re-run and recreate the database. You can use DbContext.Database.Initialize(force: true)
to force the initializer to run again.
A few days ago I wrote integration tests that included DB access through EF6. For this, I had to create and drop a LocalDB database on each test case, and it worked for me.
I didn't use EF6 database initializer feature, but rather executed a DROP/CREATE DATABASE script, with the help of this post - I copied the example here:
using (var conn = new SqlConnection(@"Data Source=(LocalDb)\v11.0;Initial Catalog=Master;Integrated Security=True"))
{
conn.Open();
var cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = string.Format(@"
IF EXISTS(SELECT * FROM sys.databases WHERE name='{0}')
BEGIN
ALTER DATABASE [{0}]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
DROP DATABASE [{0}]
END
DECLARE @FILENAME AS VARCHAR(255)
SET @FILENAME = CONVERT(VARCHAR(255), SERVERPROPERTY('instancedefaultdatapath')) + '{0}';
EXEC ('CREATE DATABASE [{0}] ON PRIMARY
(NAME = [{0}],
FILENAME =''' + @FILENAME + ''',
SIZE = 25MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB )')",
databaseName);
cmd.ExecuteNonQuery();
}
The following code was responsible for creating database objects according to the model:
var script = objectContext.CreateDatabaseScript();
using ( var command = connection.CreateCommand() )
{
command.CommandType = CommandType.Text;
command.CommandText = script;
connection.Open();
command.ExecuteNonQuery();
}
There was no need to change database name between the tests.