Pomelo.entityframeworkcore.mysql: Practical Way to use MySql Enums

[greghroberts]

Steps to reproduce

Almost everything works with MySql enums when you change the column type with the following method that you call for every property in your context:

        /// <summary>
        ///     Leverages MySql's enum type
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="property"></param>
        public static void EnumConventions(IMutableEntityType entity, IMutableProperty property)
        {
            var typeInfo = property.ClrType.GetTypeInfo();
            if (typeInfo.IsEnum && typeInfo.GetCustomAttribute<FlagsAttribute>() == null)
            {
                property.Relational().ColumnType = property.ClrType.EnumTypeToMySqlEnum();
            }
        }

        public static string EnumTypeToMySqlEnum(this Type value)
        {
            return $"ENUM('{string.Join("','", Enum.GetNames(value))}')";
        }

The issue

Persistence and migrations work fine here. The limitation is on querying as by default MySql will return a string for this column and EF blows up trying to convert that to an int. All that needs to be done for the simple scenario is to either pre-cast these columns to int when querying, and/or handling the string => enum conversion. I'm not sure which approach would be easier, but again, I'm not looking for full complete enum support, just enough hooks to make it usable.

I'm happy to help contribute, but would ask for recommendation on how to make one of these options work.

Further technical details

MySQL version: 5.7
Operating system: Ubuntu
Pomelo.EntityFrameworkCore.MySql version: 1.0

[yukozh]

Hi @greghroberts, We don't have any plan for supporting enum column type yet. It seems that the official sqlserver provider for ef core doesn't support this too. So change this issue into backlog.

[greghroberts]

Enum column types are specific to MySQL. I don't believe SqlServer has an equivalent yet. This feature should be pretty easy to do as it just needs awareness of the custom type similarly to what u are doing with JSON type.

[greghroberts]

For reference it seems the npgsql is working through some of this too. https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/27 I believe Postgres is even more complex since enums are defined as new types instead of a general enum type.

[caleblloyd]

Supporting ENUM type is still on the backlog for the ADO.NET driver we are switching to in 1.0.1.

https://github.com/bgrainger/MySqlConnector/issues/4

[bgrainger]

MySqlConnector now has support for ENUM columns in 0.7.3.

MySQL Server stores ENUM columns as integers but sends them to/from clients as strings. So MySqlConnector will deserialize them as strings and MySqlDataReader.GetValue(i) will return a string.

If you want to convert them "automagically" to System.Enum values, you will need to detect when GetDataTypeName returns "ENUM", know the type of .NET enum you wish to convert to, and call Enum.Parse(YourEnumType, reader.GetString(n), ignoreCase: true). The MySqlConnector library will not do this automatically.

[pkiers]

@Kagamine I will take a look at this one, we need it for our project.

[mguinness]

It appears that value conversions are being introduced in EF Core 2.1 which would allow storing enums as strings.

Starting with EF Core 2.1, value conversions can be applied to transform the values obtained from columns before they are applied to properties, and vice versa.

https://blogs.msdn.microsoft.com/dotnet/2018/02/02/entity-framework-core-2-1-roadmap/

[klyse]

Hi,
we where hoping to use this feature with ef core and Pomelo.EntityFrameworkCore.MySql. Is this working? or planned?

[lauxjpn]

I will implement this as our first new feature for 5.0.0.