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.

Solution 1

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!

Solution 2

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

Solution 3

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.

Comments

[Gabriel]

That much is true. I accidentally called EnsureCreated instead of Migrate. Question is, how to proceed now? Should I delete the database? The migrations? Both? Something else?