Efcore.pg: Support PostgreSQL enums

[roji]

_From @IC3Q on October 28, 2015 21:32_

I'm using Npgsql.EntityFramework7 provider, version 3.1.0-beta8-2.

The problem is while trying to get entity from context which has some enum inside of it.

Postgres enum declaration:

CREATE TYPE public.my_enum AS
 ENUM ('value1','value2','value3');

Model:

[Table("my_table")]
public class Entity
{ 
        [Column("id")]
        [Key]
        public int Id { get; set; }
        [Column("type")]
        public MyEnum Type { get; set; }
}

Enum:

public enum MyEnum
{
    value1,
    value2,
    value3
}

Unfortunately, when you try to get something from database via entity framework it throws this error:

System.InvalidCastException: Can't cast database type my_enum to Int32

I've tried to register my enum in Npgsql (as it's pointed in npgsql documentation). With no effects.

Original question on stackoverflow: http://stackoverflow.com/questions/33394908/enum-handling-in-npgsql-and-entityframework7

_Copied from original issue: npgsql/npgsql#842_

[roji]

It seems the EF7 doesn't support database enum mapping right now, mapping enums to numeric types instead. Have opened https://github.com/aspnet/EntityFramework/issues/3620 for this.

[vankooch]

Hi is there a workaround until this get fixed?

[roji]

I think you should be able to have enums on your entities, it's just that they'll be mapped to database int columns instead of PostgreSQL enums. I haven't confirmed this yet, though.

[julielerman]

Yes, I'm using enums and getting integers by default. I haven't tried mapping to a posgres enum type but I do need to flip to sqlite occasionally so leaving it as int.

[roji]

@julielerman you're not going to be able to map to a PG enum because of limitations in EFCore, this is what this issue is about. My hope is that when EFCore does support native database enums you'll be able to have the same model mapping to enum on PostgreSQL and to int on sqlite.

[julielerman]

thanks. I realize now that enum isn't just a type to specify with HasColumnType so understand why even though basic enum support is now working in EFCore, that this is a much farther stretch.

[vankooch]

@roji I can confirm that using a enum in C# and mapping it to an int in the database does work.

My problem is that we do already use enums as custom types in postgres. So I was looking for a solution for that case. Anyway I've a working solution it's not the best but it works. It does require that the enum in C# has the PgName attributes set.

Change the model like this:

[NotMapped]
public MyEnum Type
{
    get
    {
        return GetEnumValueByPgName(MyEnum.Value1, this.TypeDatabase);
    }

    set
    {
        this.TypeDatabase = value.GetPgName();
    }
}

[Column("type")]
public string TypeDatabase { get; protected set; }

This is the helper function I use:

/// <summary>
/// Gets PgName value for enum
/// </summary>
/// <param name="enumVal">The enum value</param>
/// <returns>PgName or empty if not found</returns>
public static string GetPgName(this Enum enumVal)
{
    var type = enumVal.GetType();
    var memInfo = type.GetMember(enumVal.ToString());
    if (memInfo.Count() == 0)
    {
        return null;
    }

    var attributes = memInfo[0].GetCustomAttributes(typeof(NpgsqlTypes.PgNameAttribute), false).ToArray();
    if (attributes?.Length > 0)
    {
        var name = (NpgsqlTypes.PgNameAttribute)attributes[0];
        if (name != null)
        {
            return name.PgName;
        }
    }

    return string.Empty;
}

/// <summary>
/// Get enum value by PgName. Use as workaround for bug in EfCore.
/// </summary>
/// <param name="defaultValue">Default value to set if could not find a match</param>
/// <param name="pgname">PgName used in database</param>
/// <returns>Mapped value or default value</returns>
public static TEnum GetEnumValueByPgName<TEnum>(this TEnum defaultValue, string pgname)
{
    if (string.IsNullOrEmpty(pgname))
    {
        return defaultValue;
    }

    var type = typeof(TEnum).GetTypeInfo();
    if (!type.IsEnum)
    {
        throw new TypeLoadException("Given type is not an enum.");
    }

    foreach (var item in type.GetMembers())
    {
        var attributes = item.GetCustomAttributes(typeof(NpgsqlTypes.PgNameAttribute), false).ToArray();

        if (attributes?.Length > 0)
        {
            var name = (NpgsqlTypes.PgNameAttribute)attributes[0];
            if (name != null && name.PgName == pgname)
            {
                return (TEnum)Enum.Parse(typeof(TEnum), item.Name);
            }
        }
    }

    return defaultValue;
}

[roji]

@vankooch I'm surprised this actually works, but if it does why not.

[edjacob25]

@vankooch Hey, your solution only works for retrieving right? I tried to use but I still get the Npgsql.PostgresException: 42804: column "payment_type" is of type payment_type but expression is of type text by at insertion time.

[ian-luca]

@roji Any news on when this feature will be implemented or how this issue can be bypassed?

[roji]

@CoffeeDreamLabs there is a slight chance that this will be possible in the upcoming 2.1 release, but it doesn't depend on me or on Npgsql, but rather on EF Core itself. Go to https://github.com/aspnet/EntityFrameworkCore/issues/3620 and express your interest there.

[roji]

@ajcvickers, @divega and everyone else interested in this, I've completed a hacky prototype that successfully maps to PostgreSQL native enums. Enum types can even be defined using a fluent API on the model and are created as part of migrations. I will probably complete support for this, including scaffolding, for 2.1.

It's really, really nice to be able to do all this with EF Core, great going guys!

[ajcvickers]

@roji Very cool! I have a PR out for the enum literal bug.

[roji]

@ajcvickers will wait for that to be fixed (https://github.com/aspnet/EntityFrameworkCore/pull/11041), there's more than enough to do in the meantime...

[divega]

@roji Thanks for the update. That sounds really cool! It is also great because you are helping us validate (and improve :smile:) the type mapping design.

[myrup]

@roji aspnet/EntityFrameworkCore#11041 was merged. Is there any way to start using your fix today?

[roji]

@myrup I'm actively working on this, it involves many things across the stack. I'm really hoping it'll make it into preview2.

[roji]

This has been merged and will be released with 2.1.0-preview2.

For now, the depends on Npgsql 4.0.0, which is still in development - not ideal. We'll see how the release cycles of EF Core and Npgsql coincide and see what to do.