How to map C# enum to PostgreSQL enum using Dapper.FastCRUD?

Question

I have a class Sample, one of whose properties is an enum, TargetType. I have a corresponding table samples defined in a PostgreSQL database, along with a matching enum type, targettypes.

With Dapper.FastCRUD, I can retrieve records from the table successfully. However, I get an error during insertion:

Npgsql.PostgresException (0x80004005): 42804: column "target_type" is of type targettype but expression is of type integer

EDIT 1: MoonStorm - creator of Dapper.FastCRUD - clarified that DB-CLR type conversions are handled by Dapper. So, now the question is:

How do I tell Dapper to map the C# enum TargetType to PostgreSQL ENUM TYPE targettype?

The enum is defined as:

public enum TargetType
{
    [NpgsqlTypes.PgName("Unknown")]
    UNKNOWN = 0,

    [NpgsqlTypes.PgName("Animal")]
    ANIMAL = 1,

    [NpgsqlTypes.PgName("Car")]
    CAR = 2,

    [NpgsqlTypes.PgName("Truck")]
    TRUCK = 3
}

And the class is defined as:

[Table("samples")]
public partial class Sample
{
    [Column("recording_time")]
    public DateTime RecordingTime { get; set; }

    [Column("x_position")]
    public double X_Position { get; set; }

    [Column("x_velocity")]
    public double X_Velocity { get; set; }

    [Column("y_position")]
    public double Y_Position { get; set; }

    [Column("y_velocity")]
    public double Y_Velocity { get; set; }

    [Key]
    [Column("id")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public ulong Id { get; set; }

    [Column("target_type")] // <--- This is the offending column
    public TargetType TargetType { get; set; }

}

EDIT 2: Revised the example with working insert.

Illustration of usage:

using Npgsql;
using Dapper.FastCrud;
...

NpgsqlConnection.MapEnumGlobally<TargetType>("public.targettype"); // ... (1)

OrmConfiguration.DefaultDialect = SqlDialect.PostgreSql;

(using NpgsqlConnection conn = ...) // Connect to database
{
    var samples = conn.Find<Sample>();  // <--- This works correctly
    foreach (Sample s in samples)
        Console.WriteLine(s);

    ... // Generate new samples

    using (var writer = conn.BeginBinaryImport(sql))
    {
        foreach (Sample s in entities)
        {
            writer.StartRow();

            writer.Write(s.TargetType);  // <--- This insert works, due to (1)
            ...
        }
    }

    foreach (Sample sample in sampleList)
        conn.Insert<Sample>(sample);    // <--- This throws PostgresException
    ...
}

Ben Collins · Answer 1 · 2019-04-11T16:13:34.783

I've been faced with this same problem today, and my conclusion is that Dapper currently simply doesn't support this. What you'd want to do is register a custom type handler with SqlMapper like this:

public class DbClass
{
  static DbClass()
  {
    SqlMapper.AddTypeHandler(new MyPostgresEnumTypeHandler());
  }

  class MyPostgresEnumTypeHandler : SqlMapper.TypeHandler<MyPostgresEnum>
  {
    public override MyPostgresEnum Parse(object value)
    {
      switch (value)
      {
        case int i: return (MyPostgresEnum)i;
        case string s: return (MyPostgresEnum)Enum.Parse(typeof(MyPostgresEnum),s);
        default: throw new NotSupportedException($"{value} not a valid MyPostgresEnum value");
      }
    }

    public override void SetValue(IDbDataParameter parameter, MyPostgresEnum value)
    {
      parameter.DbType = (DbType)NpgsqlDbType.Unknown;
      // assuming the enum case names match the ones defined in Postgres
      parameter.Value = Enum.GetName(typeof(MyPostgresEnum), (int)value).ToString().ToLowerInvariant(); 
    }
  }
}

Unfortunately, this doesn't work because Dapper ignores custom type handlers for Enums, specifically. See https://github.com/StackExchange/Dapper/issues/259 for details.

My approach while waiting to see if this issue is ever dealt with is going to be to write NpgsqlCommand queries directly when dealing with these kinds of enums.

score 0 · Answer 2 · answered Oct 06 '16 at 16:48

0

You probably have to convert TargetType to a integer.

Untested but something like:

Get 
{
    return (int)this.TargetType;
}

answered Oct 06 '16 at 16:48

David Ulvmoen

87
1
3

I think it's the converse problem. The error message suggests that Npgsql sees an `integer` whereas it expects a `targettype`. The enum `targettype` is defined in the postgres database. I feel I need to inform Dapper/Npgsql that `sample.TargetType` is of type `targettype`. Does that make sense? – Abir Oct 06 '16 at 21:54
http://www.npgsql.org/doc/enums_and_composites.html NpgsqlConnection.MapEnumGlobally(); However take a look at dapper contrib, if you can use postgre without annoying mapping (possible on sql server). – David Ulvmoen Oct 08 '16 at 11:09

How to map C# enum to PostgreSQL enum using Dapper.FastCRUD?

2 Answers2

Linked