如何使用Dapper.FastCRUD将c#枚举映射到PostgreSQL枚举
本文关键字:枚举 映射 PostgreSQL 何使用 Dapper FastCRUD | 更新日期: 2023-09-27 18:11:36
我有一个类Sample
,其属性之一是enum, TargetType
。我在PostgreSQL数据库中定义了一个相应的表samples
,以及一个匹配的枚举类型targettypes
。
。通过FastCRUD,我可以成功地从表中检索记录。但是,我在插入时得到一个错误:
Npgsql.PostgresException (0x80004005): 42804: column "target_type" is of type targettype but expression is of type integer
EDIT 1: MoonStorm - Dapper的创造者。FastCRUD -澄清了DB-CLR类型的转换是由Dapper处理的。那么,现在的问题是:
我如何告诉Dapper映射c#的enum TargetType
到PostgreSQL的ENUM TYPE targettype
?
枚举定义为:
public enum TargetType
{
[NpgsqlTypes.PgName("Unknown")]
UNKNOWN = 0,
[NpgsqlTypes.PgName("Animal")]
ANIMAL = 1,
[NpgsqlTypes.PgName("Car")]
CAR = 2,
[NpgsqlTypes.PgName("Truck")]
TRUCK = 3
}
类定义为:
[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:修改了使用工作插入的示例。
用法说明:
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
...
}
我今天遇到了同样的问题,我的结论是Dapper目前根本不支持这一点。您需要做的是像这样用SqlMapper
注册一个自定义类型处理程序:
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();
}
}
}
不幸的是,这不起作用,因为Dapper特别忽略了enum的自定义类型处理程序。详见https://github.com/StackExchange/Dapper/issues/259
在等待这个问题是否得到处理的同时,我的方法是在处理这些类型的枚举时直接编写NpgsqlCommand
查询。
您可能需要将TargetType转换为整数。
未经测试,但类似于:
Get
{
return (int)this.TargetType;
}