EF-Core: Table "name" already exists - when trying to update database

Question

ASP Core 3.1 - API. I'm using the latest version of Entity Framework Core.

I have created a table ToDoItem and a ToDoItemContext. After creating the initial migration, and running update-database. I now have that table in my database. I now added a new model called: ToDoItemDescription.

When I try to update the database after creating a new migration, I get the error:

Table 'todoitems' already exists

Further details: I have two contexts, and this is the command I ran:

update-database -context todoitemscontext

I also tried:

update-database -context todoitemscontext -migration AddDescription

Here is my full code:

Models:

public class TodoItem : IEntity 
{
    public long Id { get; set; }
    public string Name { get; set; }
    bool IsComplete { get; set; }
}

public class ToDoItemDescription 
{
    public int id { get; set; }
    public string Description { get; set; }
    //public int ToDoItemId { get; set; }
    public TodoItem TodoItem { get; set; }
}

Context:

public class TodoItemsContext : DbContext 
{
   public TodoItemsContext(DbContextOptions<TodoItemsContext> options) : base(options) { }

   public DbSet<TodoItem> TodoItems { get; set; }
   public DbSet<ToDoItemDescription> TodoItemsDescription { get; set; }
}

Migrations:

[DbContext(typeof(TodoItemsContext))]
partial class TodoItemsContextModelSnapshot : ModelSnapshot 
{
    protected override void BuildModel(ModelBuilder modelBuilder) {
    #pragma warning disable 612, 618
    modelBuilder
        .HasAnnotation("ProductVersion", "3.1.9")
        .HasAnnotation("Relational:MaxIdentifierLength", 64);
    modelBuilder.Entity("project.Models.ToDoItemDescription", b => {
        b.Property<int>("id")
        .ValueGeneratedOnAdd()
        .HasColumnType("int");
        b.Property<string>("Description")
        .HasColumnType("longtext CHARACTER SET utf8mb4");
        b.Property<long?>("TodoItemId")
        .HasColumnType("bigint");
        b.HasKey("id");
        b.HasIndex("TodoItemId");
        b.ToTable("TodoItemsDescription");
    });

    modelBuilder.Entity("project.Models.TodoItem", b => {
        b.Property<long>("Id")
        .ValueGeneratedOnAdd()
        .HasColumnType("bigint");
        b.Property<bool>("IsComplete")
        .HasColumnType("tinyint(1)");
        b.Property<string>("Name")
        .HasColumnType("longtext CHARACTER SET utf8mb4");
        b.HasKey("Id");
        b.ToTable("TodoItems");
    });
    modelBuilder.Entity("project.Models.ToDoItemDescription", b =>
    {
    b.HasOne("project.Models.TodoItem", "TodoItem")
        .WithMany()
        .HasForeignKey("TodoItemId");
    });
#pragma warning restore 612, 618
}

public partial class TodoItems_Initial : Migration
{
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "TodoItems",
                columns: table => new
                {
                    Id = table.Column<long>(nullable: false)
                        .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
                    Name = table.Column<string>(nullable: true),
                    IsComplete = table.Column<bool>(nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_TodoItems", x => x.Id);
                });
        }

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

public partial class AddDescription : Migration
{
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "TodoItemsDescription",
                columns: table => new
                {
                    id = table.Column<int>(nullable: false)
                        .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
                    Description = table.Column<string>(nullable: true),
                    TodoItemId = table.Column<long>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_TodoItemsDescription", x => x.id);
                    table.ForeignKey(
                        name: "FK_TodoItemsDescription_TodoItems_TodoItemId",
                        column: x => x.TodoItemId,
                        principalTable: "TodoItems",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateIndex(
                name: "IX_TodoItemsDescription_TodoItemId",
                table: "TodoItemsDescription",
                column: "TodoItemId");
        }

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

Thank you.

Answer 1

This usually happens when you have a migration that creates a table and the required table is already present in your database so, when you update the database from classes in Migration, it will try to create a table and will fail because the Create command will not be executed as it already has that specific table.

So, in order to avoid the error, you might want to remove the migration class or comment the code in Up() method of that class so it doesn't execute that specific create command.

Or you may want to drop the table from your database and then run migration. This way you may lose your data if the table was already in use from some other connection.

The other solution maybe to move your table creation logic in an sql statement in your Up() method something like this:

migrationBuilder.Sql(@"
IF OBJECT_ID('dbo.TodoItems', 'U') IS NULL 
BEGIN
    CREATE TABLE TodoItems (
        [Id] bigint identity(1,1) not null PRIMARY KEY,
        [Name] nvarchar(100),
        [IsComplete] bit not null
    )
    -- Indexes if needed and so on...
END
");

The right approach depends on how you want to go by based on your specific needs in the project.

Answer 2

DbContext.Database.EnsureCreated() is causing this error!! (why?? idk i'm a noob learning C#). ALWAYS since the beginning make sure to only use dotnet/ef core CLI to avoid any bugs.

---

If you already used DbContext.Database.EnsureCreated() go to psql terminal for PostgreSQL (or the equivalent you're using) and run \d (or the equivalent) to display all tables names and for each one (except for __EFMigrationsHistory) run

DROP TABLE <table_name>;

(Before DROPing all the tables, I had to delete some migrations to fix this bug. If they're not important I recommend deleting all of them straight away to fix it all at once. use dotnet ef migrations list to see all migrations and plan what you will do.)

---

Now to make sure you solved the problem, use `dotnet ef database update` to check everything is working well, it shouldn't fail. You can now perform your migrations in the recommended way:

dotnet ef migrations add <MigrationName>
dotnet ef database update

instead of DbContext.Database.EnsureCreated() to change your schema. This way I never had any more problems.

Answer 3

My case was that I used DbContext.Database.EnsureCreated() (which I removed after I found the solution) in the ctor of my every dbcontext, as a result, I dumped (saved) my dbs as json, I dropped and deleted everything via MySQL, deleted migrations folder in the project directory, then made fresh migrations, then I made temp post request to upload all the data (json which I dumped) and now everything in the dbs as before.

Answer 4

The issue with migrations is it reapplies any migrations that are not captured in _EFMigrationsHistory table.

Those are the migrations in Visual Studio, I am trying to apply migration_02 ONLY, but it keeps running migration_01 which causes similar issues!

When looking at _EFMigrationsHistory, it's obviously way off!

I took a risk and modified the data to: (using migration_01 full name)

Finally! managed to run the migration, only migration_02 changes applied, and the table now shows:

Answer 5

I was working through the migration tutorial and had made a mistake sometimes around these steps

    dotnet ef migrations add AddBlogCreatedTimestamp
    dotnet ef database update

I did the following

1. deleted the files AddBlogCreatedTimestamp.Designer.cs and AddBlogCreatedTimestamp.cs

2. inside blogging.db in the table __EFMigrationsHistory i deleted the row that contains 2023__***__AddBlogCreatedTimestamp this was the migration step that failed.

3. I repeated the migration step dotnet ef migrations add ...

4. then manually added DropTable(...) to AddBlogCreatedTimestamp.Up()

5. only then i ran dotnet ef database update

This made sure that in an up-migration the tables would be deleted

Code manually changed

public partial class AddBlogCreatedTimestamp : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
       // manually added
       migrationBuilder.DropTable(name: "Posts");
       migrationBuilder.DropTable(name: "Blogs");
       // ... other lines that were created
    }
    
    // more other code ...
}

What i still not get is why this is needed. I am not aware to have used anything like EnsureCreated

Answer 6

It could possible help people working with MySQL databases either on Linux and Windows

TL;DR;

I had to rename the table

• __efmigrationshistory (note the lowercase) to
• __EFMigrationsHistory (note the case)

so the command-line dotnet-ef database update managed to verify all the migrations present on the table __EFMigrationsHistory, and therefore, creating the new field on the table, say Tenant

More

• I have to work on Linux, Windows, MacOs boxes. Primarily using Visual Studio code and .net core 3.1.xxx
• I use the code-first approach. The MySQL database was firstly, create on the Windows box, where all the tables were created lower cased
• Switching to the Linux box, I realized the case was important, so, say, table "tenant" was renamed to "Tenant", by hand.
• Once I had to create a new field on the Tenant's c# class, I ran: dotnet-ef migrations add new-ftpSettings-field and dotnet-ef database update, I got table "Order" already exists. Note I was trying to insert a new field to the "Tenant" table
• After a lot of investigation and search, I decided to refresh the database again, and I saw "two suspicious tables" __efmigrationshistory and __EFMigrationsHistory.
• I renamed the empty table __EFMigrationsHistory to like Table1 (as a backup), and thus renamed the table __efmigrationshistory to __EFMigrationsHistory
• I ran the dotnet-ef database update and the field was properly added to the MySQL database.

*** Like you might have figured this out, running the command-line dotnet-ef database update on Linux was creating a new (and) empty table __EFMigrationsHistory to MySQL database while it had already, a lower cased table on __efmigrationshistory (the good one, created on my Windows box, with all the migrations).

*** This is my first contribution. Any advice is welcome!

Keep safe! Tchau/Au revoir!

Answer 7

This happens if you have created the database upfront without migrations, for example by using DbContext.Database.EnsureCreated();.