MySQL Bugs: #79445: EF6: Wrong type of generated key fields/Broken foreign keys in AlterColumn

Bug #79445	EF6: Wrong type of generated key fields/Broken foreign keys in AlterColumn
Submitted:	28 Nov 2015 20:56	Modified:	2 Jan 2016 7:17
Reporter:	Marek Lemanczyk	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.9.5-6.9.8	OS:	Windows (.Net 4.5.2, Entify Framework 6.1.3)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Hello,

It seems that .Net Connector uses wrong type for integer (short, int etc.) fields. All the fields marked as key and database generated are defined in MySQL as UNSIGNED, 1 byte smaller than their SIGNED representation. In addition, manual override of the type (storeType) doesn't mark the field as SIGNED.

But their corresponding foreign fields in other classes are declared as SIGNED integer fields, 1 byte bigger than UNSIGNED.

This breaks EF migrations and their ability to define foreign keys, but also to use such a table in EF. To workaround the issue, the migrations need to be modified to execute raw SQL ALTER TABLE, instead of using AlterTable.

How to repeat:
Simple EF model:

    [Table("currencies")]
    public partial class Currency
    {
        [DataMember]
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long CurrencyId { get; set; }
    }

    [Table("prices")]
    public partial class Price
    {
        [DataMember]
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long PriceId { get; set; }

        [DataMember]
        [Required]
        [DataType(DataType.Currency)]
        public decimal CurrentPrice { get; set; }

        [DataMember]
        public long CurrencyId { get; set; }
        [DataMember]
        [ForeignKey("CurrencyId")]
        public virtual Currency Currency { get; set; }
    }

Once the database is created, when you try to alter the columns and change Currencies.CurrencyId & Prices.CurrencyId types, e.g. to short we'll get:
  Currencies.CurrencyId SMALLINT(5) UNSIGNED
  Prices.CurrencyId SMALLINT(6)

Suggested fix:
Since Entity Framework doesn't support unsigned integers, all generated integer fields should be SIGNED.

Workaround:
Generate raw SQL and create the table and/or alter column integer columns manually instead of using AlterColumn in the migrations.

Hello Marek Lemanczyk,

Thank you for the bug report.
I tried to reproduce the issue at my end using Visual Studio 2013 (C#.Net), MySQL Connector/Net 6.9.5 to 6.9.8 but not seeing any issue in generated key fields.
Could you please provide repeatable test case (exact model, context class, etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".