Bug #71418 AddColumn with identity true doesn't work on .NET Connector 6.8.3
Submitted: 19 Jan 2014 10:04 Modified: 9 Jan 2015 9:45
Reporter: Tiger WU Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.8.3 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[19 Jan 2014 10:04] Tiger WU
Description:
 public partial class Migration2 : DbMigration
    {
        public override void Up()
        {
            RenameColumn(table: "DeviceUsage", name: "id", newName: "EquipmentuseID");
            DropPrimaryKey("DeviceUsage", new[] { "id" });
            AddColumn("DeviceUsage", "_id", c => c.Int(nullable: false, identity: true));
        }
....
}

The above code works well with EF5 and .NET Connector 6.7.4; however with EF6 and Connector 6.8.3, an error "Incorrect table definition; there can be only one auto column and it must be defined as a key" was reported. 

Different ways of handling the integer identity column are in MySqlMigrationCodeGenerator#Generate(ColumnModel op) of the two versions:

V6.7.4:
if (op.IsIdentity && type == "int")
      {
        sb.Append(" auto_increment primary key ");
      }

V6.8.3:
      if (op.IsIdentity && (new string[] { "tinyint", "smallint", "mediumint", "int", "bigint" }).Contains(type.ToLower()))
      {
        sb.Append(" auto_increment ");
        autoIncrementCols.Add(op.Name);
      }

V6.8.3 Adds the column to autoIncrementCols set, which is only handled in CreateTable statement, so AddColumn statment can't recognize the column as a primary key or key.

How to repeat:
1. Define an entity(Class: DeviceUsage) with a string property(Name: EquipmentuseID) mapped to primary key(Name: id) in database
    Class DeviceUsage{
        [Key("id")]
        public string EquipmentuseID {get; set;}

        public string OtherProperty {get; set;}
    }

2. After the entity is mapped to a table, change the entity class defintion to 
    Class DeviceUsage{
        [Key("_id")]
        public int id {get; set;}

        public string EquipmentuseID {get; set;}

        public string OtherProperty {get; set;}
    }
and construct the migration code as in the description
3. Apply the migration