EF6 Code First drop tables (not entire database) when model changes

Question

I'm doing Code First development with Entity Framework 6, using Database Migrations, and I'm using a new database that is populated with sample seed data. I'd like to be able to initialize my database with that seed data any time I change the model.

The catch is this: I don't have database create permissions; because of this, I can't just utilize DropCreateDatabaseIfModelChanges.

Is there a way that I can programmatically drop all of my tables, or am I stuck manually deleting them from the database each time?

possible duplicate of [Recreate entity framework tables, not databases?](http://stackoverflow.com/questions/7358673/recreate-entity-framework-tables-not-databases) — TheNorthWes, Jun 13 '14 at 15:49

score 3 · Accepted Answer · edited May 23 '17 at 12:16

3

Ultimately, I didn't need to delete the tables, just the data they contained.

I ended up solving this by simply truncating a list of tables at the beginning of my Seed method, based on this answer.

protected override void Seed(MyContext context)
{
    var listOfTables = new List<string> { "Table1", "Table2", "Table3" };

    foreach (var tableName in listOfTables)
    {
        context.Database.ExecuteSqlCommand("TRUNCATE TABLE [" + tableName + "]");
    }

    context.SaveChanges();

    // seed data below
}

edited May 23 '17 at 12:16

Community

1
1

answered Jun 16 '14 at 15:33

Gunner Barnes

385
1
4
18

1

Aren't you going to have issues with FK constraints? – Mike Cole Jun 16 '14 at 15:45
1

I didn't have to truncate all tables in my situation, and that ones that I did truncate didn't have any FK constraints. If I did have to worry about that, I would make sure to set up my list in proper order. – Gunner Barnes Jun 16 '14 at 15:50

score 2 · Answer 2 · answered Jun 14 '14 at 02:18

If you're not using automatic migrations, but code based migrations, you can back all the way down to the first version using the follow command:

Update-Database –TargetMigration: 0

This will follow the Down path on all of your migrations until you have a clean database. Then you can execute:

Update-Database

This will bring everything back up to date. This solution assumes you've properly maintained your Down path and seeded your data with Migrations. I do this for my integration tests to ensure I start with the data in an expected state.

I was hoping this would work for me, but one of my later migrations introduced an additional identity property to a composite primary key (for 1..n support). Ideally, I would wipe the migrations and add a fresh initial migration, but I'm not certain the schema is in a final state yet. — Gunner Barnes, Jun 16 '14 at 15:41

score 1 · Answer 3 · answered Jun 13 '14 at 15:58

My suggestion is to use the local DB or another DB you have full permission on (Azure is nice, and free if you have a MSDN account). Then migrate that final DB schema over once it's set in stone and ready for production.

That being said, this might be helpful, but I've never tried it before.

score 0 · Answer 4 · answered Jun 14 '14 at 02:05

If you dont have permission access to the database, it may be better to address that issue. Anyway:

  public bool TruncateTable(string connectionString, string schema, string tableName) {
        var statement = "TRUNCATE TABLE [{0}].[{1}] ;";
        statement = string.Format(statement, schema, tableName);
        return ExecuteSqlStatement(connectionString, statement);
    }

    public bool DeleteAllEntriesTable(string connectionString, string schema, string tableName) {
        var statement = "DELETE FROM [{0}].[{1}] ;";
        statement = string.Format(statement, schema, tableName);
        return ExecuteSqlStatement(connectionString, statement);
    }

    public bool ExecuteSqlStatement(string connectionString, string statement, bool suppressErrors = false) {
        int rowsAffected;
        using (var sqlConnection = new SqlConnection(connectionString)) {
            using (var sqlCommand = new SqlCommand(statement, sqlConnection)) {
                try {
                    sqlConnection.Open();
                    rowsAffected = sqlCommand.ExecuteNonQuery(); // potential use
                }
                catch (Exception ex) {
                    if (!suppressErrors) {
                     // YOUR ERROR HANDLER HERE
                    }

                    return false;
                }
            }
        }

        return true;

EF6 Code First drop tables (not entire database) when model changes

4 Answers4