Repeatedly creating and deleting databases in Entity Framework

.net c# database entity-framework-6 sql-server

Question

I discovered some strange behavior in EF6 (tested with 6.1 and 6.1.2) when developing some unit tests for our application. Apparently, it is not feasible to repeatedly create and destroy databases (with the same name/connection string) within the same application context.

the 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

}

Only RepeatedCreateDeleteDifferentName succeeds; the other two fall short. As a result, you are unable to establish a database using a name that has already been used once. The test (and application) throws a SqlException noting a failed login while attempting to build the database for the second time. Is this an intended behavior on the part of Entity Framework, and if so, why?

I haven't yet tested this with Oracle; I used Microsoft SQL Server 2012 and Express 2014. By the way, the fact that CompatibleWithModel is the initial call to the database appears to be problematic for EF.

Submitted a bug report on the EF bug tracker (link)

1
9
2/2/2015 9:07:40 AM

Accepted Answer

Only one database initializer is executed for each context and AppDomain. Therefore, they won't automatically re-run and recreate the database if you destroy it at some random moment. You may utilizeDbContext.Database.Initialize(force: true) to make the initializer run once again.

4
2/10/2015 11:42:48 PM

Popular Answer

I recently created integration tests with DB access using EF6. This worked for me; all I had to do was create and drop a LocalDB database on each test case.

Instead of using the EF6 database initializer feature, I used these words to execute a DROP/CREATE DATABASE script. I've pasted the sample 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 model-based creation of database objects was handled by the code listed below:

var script = objectContext.CreateDatabaseScript();

using ( var command = connection.CreateCommand() )
{
    command.CommandType = CommandType.Text;
    command.CommandText = script;

    connection.Open();
    command.ExecuteNonQuery();
}

Between experiments, the database name didn't need to be changed.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow