Pomelo.EntityFrameworkCore.MySql

Pomelo.EntityFrameworkCore.MySql copied to clipboard

Steps to reproduce

Configure an Id column on a table as a MEDIUMINT UNSIGNED with an auto-generated value.

    b.Property<int>("Id")
     .ValueGeneratedOnAdd()
     .HasColumnType("MEDIUMINT UNSIGNED");

Try to insert a record in the table via EF. The exception below is thrown.

The issue

Initially asked here: https://stackoverflow.com/q/50778720/2343739

This could very well be an EF Core 2.1 issue and not a Pomelo issue...

Since upgrading an application to EF Core 2.1, I have started getting the exception below. I suspect that it has something to do with the new Value Conversions (though I haven't manually implemented a ValueConverter). I want to maintain database-driven auto increment values for the Snapshot.Id column mentioned. The same code-base works fine on EF Core 2.0. The database schema was all built via EF Core code-first.

My temporary workaround: https://github.com/collinbarrett/FilterLists/commit/8fd420556af33e2d85b69122f5a5c5d15ffb8d22 . This allows the application to run, but will cause issues with EF migrations since I actually don't want to remove the UNSIGNED from the database column.

Exception message:

NotSupportedException: Value generation is not supported for property 'Snapshot.Id' because it has a 'CastingConverter' converter configured. Configure the property to not use value generation using 'ValueGenerated.Never' or 'DatabaseGeneratedOption.None' and specify explict values instead.

Further technical details

MySQL version: 10.1.33-MariaDB-1~xenial Operating system: Ubuntu 16.04 Pomelo.EntityFrameworkCore.MySql version: 2.1.0-rc1-final

We were having the same problem. A workaround is to remove the HasColumnType() function call.

b.Property<int>("Id")
     .ValueGeneratedOnAdd()
     .HasColumnType("MEDIUMINT UNSIGNED");

You are mapping a signed int in C# to an unsigned int in the database. You have a CLRType mismatch.

Either change int to uint or MEDIUMINT UNSIGNED to MEDIUMINT

@Giantaxe Hmm, that makes sense to some extent, But, using MEDIUMINT UNSIGNED is a data layer implementation detail that I don't necessarily want leaking throughout my whole application. If I ever switch data stores, I may no longer want uints. I'd rather work with just basic int throughout my dotnet app and just have the edge of EF do the conversion. Prior to dotnet/EF Core 2.1, EF seemed to handle this for me. But, I suspect the work on this changed things. Maybe I have to now explicitly provide a value conversion in my entity type configuration...?

@collinbarrett design decisions are ultimate made on what kind of data you are going to store. If you preffer using ints. then I take it your database will never actually go over Int32.MaxValue/2.147.483.647 in which case it shouldn't matter that you use a signed int in the database.

If you do plan to go over that (in which case yes, you need an unsigned mediumint) then your application layer should correspond to that aswell.

This is a current limitation of value converters in EF, tracked by https://github.com/aspnet/EntityFrameworkCore/issues/11597