How to drop a table in Entity Framework Code First?

Question

I am using Entity Framework with Auto Migrations.

So when I add a new model to my Context, my database is updated and new table is created.

What I want to do is the opposite, droping the table completely from database. However, removing the definition from Context class does not work.

public class CompanyContext : DbContext
{
    public DbSet<Permission> Permissions { get; set; }
    public DbSet<Company> Companies { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
       base.OnModelCreating(modelBuilder);
    }
}

For instance, I want to remove Company table from database. To do that, I remove Companies property from CompanyContext class. However, it is not working.

What is the proper way to drop tables in EF and using auto migrations if possible ?

Solution 1

The add-migrations command creates a Migrations folder. You can see [DateStamp]_InitialCreate.cs file containing two methods viz.; Up and Down. The Up method of the InitialCreate class creates the database tables that corresponds to the data model entity sets, and the Down method delete them. Typically when you enter a command to rollback a database, Down method is called. You can see statements like DropIndex, DropForeignKey, DropTable statements in Down method.

In case of question asked by Emre, write the DropTable statement in the Down method of [DateStamp]_InitialCreate.cs class and the table will be dropped.

Hopefully it will help.

Solution 2

Add AutomaticMigrationDataLossAllowed = true; to the Configuration class and it will drop tables automatically.

Solution 3

I did also have similar issue. Suppose your initial migration creates a table, named 'SampleTable'

    public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "SampleTable",
                columns: table => new
                {
                    ...
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_SampleTable", x => x.Id);
                });       
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "SampleTable");
        }
    }

And you want to drop that table. The steps you may need to follow are

1. Remove model from dbcontext.
2. Run command dotnet ef migrations add RemoveSampleTable (or any name of the migration). But it would generate only empty migration.

    public partial class RemoveSampleTable : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
        }
    }

3. And if you apply the migration by executing command like dotnet ef database update. It will basically do nothing. So the trick here is to copy the content of Up/Down from earlier migration and paste it to opposite method.

    public partial class RemoveSampleTable : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "VesselBuildingProject");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
                    migrationBuilder.CreateTable(
                        name: "SampleTable",
                        columns: table => new
                        {
                            ...
                        },
                        constraints: table =>
                        {
                            table.PrimaryKey("PK_SampleTable", x => x.Id);
                        });
        }
    }

4. Now if you've already tried the migration on your db by now, you need to remove that entry from __EFMigrationsHistory.
5. Now go ahead with executing command dotnet ef database update and see the magic :P

Solution 4

You should implement your "IDatabaseInitializer" and create custom logic to do this operation. For example, please visit this

Also please see "How do I use Entity Framework in Code First Drop-Create mode?" if it can help.

In my own experience, I have done it by running the below statement from Package console manager of VS 2010

update-database -StartupProjectName "Your Project Namespace" -script -Verbose –force

Make sure you have "Your Project Namespace" selected as a default project.

View more solutions

Comments

[emre nevayeshirazi]

As far as I know, DropCreateDatabaseAlways drops the whole database and recreates it. As a result you lost data. What I want to do is to drop the single table only.

[CodeMad]

Then why don't you update your Model and run the package manager console command mentioned above?

[emre nevayeshirazi]

I did that. I removed the model classes from DbContext class and I also deleted the model file itself. I thought automatic migrations should handle the rest. It is not.

[CodeMad]

Before analyzing any further, I'm just getting a blind idea. You can make use of DropCreateDatabaseAlways to delete and re-create database and let your code run the sql seed file automatically to insert the data. (You may as well generate the script before running "DropCreateDatabaseAlways")