EF数据库迁移时反复删除Issue列报错：无法删除'Issue'，请检查列/键是否存在

问题现象 #

执行EF Core数据库迁移时，实体类、DbContext和迁移代码中均明确定义了Issue列，但每次迁移都会尝试删除该列，抛出错误：Can't DROP 'Issue'; check that column/key exists。

相关代码片段 #

迁移创建表代码 #

migrationBuilder.CreateTable(
        name: "ticket",
        columns: table => new
        {
            Id = table.Column<int>(type: "int", nullable: false),
            IssueCategoryId = table.Column<long>(type: "bigint", nullable: true),
            Issue = table.Column<string>(type: "varchar(95)", maxLength: 95, nullable: true),
            AssignedTo = table.Column<string>(type: "varchar(95)", maxLength: 95, nullable: true),
            DateCreated = table.Column<DateTime>(type: "datetime", nullable: true),
            DateResolved = table.Column<int>(type: "int", nullable: true),
                            
            Solved = table.Column<bool>(type: "tinyint(1)", nullable: false),
            UserId = table.Column<string>(type: "varchar(95)", maxLength: 95, nullable: true)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Tickets", x => x.Id);
            table.ForeignKey(
                name: "FK_ticket_aspnetusers_userid",
                column: x => x.UserId,
                principalTable: "aspnetusers",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
            table.ForeignKey(
                name: "FK_ticket_assigned_aspnetusers_userid",
                column: x => x.AssignedTo,
                principalTable: "aspnetusers",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
            table.ForeignKey(
                name: "FK_ticket_issue_category_issue_category_id",
                column: x => x.IssueCategoryId,
                principalTable: "issue_category",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
        });

DbContext配置代码 #

modelBuilder.Entity<Ticket>(entity =>
    {
        entity
            .HasKey(e => e.Id).HasName("PRIMARY");
            entity.ToTable("ticket");

        entity.HasIndex(e => e.IssueCategoryId, "IssueCategoryId");

        entity.Property(e => e.AssignedTo)
            .HasMaxLength(95)
            .HasColumnName("AssignedTo");
        entity.Property(e => e.DateCreated)
            .HasDefaultValueSql("'NULL'")
            .HasColumnType("datetime")
            .HasColumnName("DateCreated");
        entity.Property(e => e.DateResolved)
            .HasDefaultValueSql("'NULL'")
            .HasColumnType("int(11)")
            .HasColumnName("DateResolved");
        entity.Property(e => e.Id)
            .HasColumnType("int(11)")
            .HasColumnName("Id");
        entity.Property(e => e.Issue)
       .HasMaxLength(255) // Adjust the maximum length as needed
       .HasColumnName("Issue");
       
        entity.Property(e => e.IssueCategoryId)
            .HasColumnType("bigint(20)")
            .HasColumnName("IssueCategoryId");
        entity.Property(e => e.Solved).HasColumnName("Solved");
        entity.Property(e => e.UserId)
            .HasMaxLength(95)
            .HasColumnName("UserId");

        entity.HasOne(d => d.IssueCategory).WithMany()
            .HasForeignKey(d => d.IssueCategoryId)
            .OnDelete(DeleteBehavior.Restrict)
            .HasConstraintName("IssueCategoryId");

        entity.HasOne(t => t.AssignedToNavigation)
          .WithMany(u => u.TicketAssignedToNavigations)
          .HasForeignKey(t => t.AssignedTo)
          .OnDelete(DeleteBehavior.Restrict);
    });

Ticket实体类代码 #

public partial class Ticket
{
    public int Id { get; set; }

    public long? IssueCategoryId { get; set; }

    public string? Issue { get; set; }
   
    public string? AssignedTo { get; set; }

    public DateTime? DateCreated { get; set; }

    public int? DateResolved { get; set; }

    public bool Solved { get; set; }

    public string? UserId { get; set; }

    public virtual Aspnetuser? AssignedToNavigation { get; set; }

    public virtual IssueCategory? IssueCategory { get; set; }

    public virtual Aspnetuser? User { get; set; }
}

生成的迁移文件代码 #

public partial class dbtes : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<string>(
            name: "Issue",
            table: "ticket",
            type: "varchar(95)",
            maxLength: 95,
            nullable: true,
            oldClrType: typeof(string),
            oldType: "varchar(255)",
            oldMaxLength: 255,
            oldNullable: true)
            .Annotation("MySql:CharSet", "utf8mb4")
            .OldAnnotation("MySql:CharSet", "utf8mb4");
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<string>(
            name: "Issue",
            table: "ticket",
            type: "varchar(255)",
            maxLength: 255,
            nullable: true,
            oldClrType: typeof(string),
            oldType: "varchar(95)",
            oldMaxLength: 95,
            oldNullable: true)
            .Annotation("MySql:CharSet", "utf8mb4")
            .OldAnnotation("MySql:CharSet", "utf8mb4");
    }
}

问题原因与解决步骤 #

1. 核心原因 #

DbContext中Issue列配置的最大长度为255，而迁移创建表时设置的是95，EF Core对比模型与迁移历史时，生成了修改长度的操作。若数据库中原本无Issue列，该操作会被误解析为删除/重建逻辑，触发报错。

2. 解决步骤 #

• 统一长度配置：将DbContext中Issue列的HasMaxLength(255)改为HasMaxLength(95)，与迁移代码保持一致：

  entity.Property(e => e.Issue)
     .HasMaxLength(95)
     .HasColumnName("Issue");
  

• 清理错误迁移：删除生成的dbtes迁移文件，避免迁移历史与模型冲突。
• 重新生成迁移：执行Add-Migration命令生成新迁移，此时迁移会正确创建Issue列或修改长度（若列已存在）。
• 执行迁移：运行Update-Database完成数据库更新。

3. 额外检查 #

• 确认数据库ticket表是否已存在Issue列，若不存在，可手动添加或确保新迁移包含创建逻辑。
• 检查__EFMigrationsHistory迁移历史表，清理残留的错误迁移记录（操作前备份数据）。

内容的提问来源于stack exchange，提问作者TheColonel