I am trying to use Dapper and Dapper-Extensions and to serialize my enums on the database as string.
Right now they are serialized as integers (inside a VARCHAR field) instead.
Is there any way to do this? Any custom type mapping that I can add?
I might need to move back to EF if i can't pull this through..
There's a way, which I think is more robust and clean.
The solution I provide will work for any enumeration, but it involves some extra coding. It also involves adding a custom type handler in Dapper. However, if this answer gets some votes, I will change the Dapper source code to include this solution automatically in the type handling and ask for a pull request.
I actually implemented this solution and use it in production.
Here goes.
First the struct (not a class, because the struct simply holds a string reference) that will be used as enumeration:
public struct Country
{
string value;
public static Country BE => "BE";
public static Country NL => "NL";
public static Country DE => "DE";
public static Country GB => "GB";
private Country(string value)
{
this.value = value;
}
public static implicit operator Country(string value)
{
return new Country(value);
}
public static implicit operator string(Country country)
{
return country.value;
}
}
Now we need a type handler for this struct
public class CountryHandler : SqlMapper.ITypeHandler
{
public object Parse(Type destinationType, object value)
{
if (destinationType == typeof(Country))
return (Country)((string)value);
else return null;
}
public void SetValue(IDbDataParameter parameter, object value)
{
parameter.DbType = DbType.String;
parameter.Value = (string)((dynamic)value);
}
}
Somewhere in the startup of the application we have to register the type handler with Dapper
Dapper.SqlMapper.AddTypeHandler(typeof(Country), new CountryHandler());
Now you can simply use Country as an "enum". For instance:
public class Address
{
public string Street { get; set; }
public Country Country { get; set; }
}
var addr = new Address { Street = "Sesamestreet", Country = Country.GB };
The downside of course is that the enumeration is not backed in memory by an integer but by a string.
Thanks to Marc Gravell reply:
The only way is to do the inserts manually.
Also using the following post: How do I perform an insert and return inserted identity with Dapper?
Below my solution.
Note that selects work automatically: you can use Dapper (Extensions) directly GetList<T>, there is no mapping to the enum back required.
public enum ComponentType
{
First,
Second,
Third
}
public class Info
{
public int Id { get; set; }
public ComponentType InfoComponentType { get; set; }
public static void SaveList(List<Info> infoList)
{
string ConnectionString = GetConnectionString();
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
foreach (Info info in infoList)
{
string sql = @"INSERT INTO [Info] ([InfoComponentType])
VALUES (@InfoComponentType);
SELECT CAST(SCOPE_IDENTITY() AS INT)";
int id = conn.Query<int>(sql, new
{
InfoComponentType = info.InfoComponentType.ToString()
}).Single();
info.Id = id;
}
conn.Close();
}
}
}
My technique is simliar to neeohw's but lets me use real enums. And it's generic so I don't have to write it many times.
There's an immutable struct that wraps the enum value. It has a single property and implicit conversions, plus a generic custom type handler.
public readonly struct DapperableEnum<TEnum> where TEnum : Enum
{
[JsonConverter(typeof(StringEnumConverter))]
public TEnum Value { get; }
static DapperableEnum()
{
Dapper.SqlMapper.AddTypeHandler(typeof(DapperableEnum<TEnum>), new DapperableEnumHandler<TEnum>());
}
public DapperableEnum(TEnum value)
{
Value = value;
}
public DapperableEnum(string description)
{
Value = EnumExtensions.GetValueByDescription<TEnum>(description);
}
public static implicit operator DapperableEnum<TEnum>(TEnum v) => new DapperableEnum<TEnum>(v);
public static implicit operator TEnum(DapperableEnum<TEnum> v) => v.Value;
public static implicit operator DapperableEnum<TEnum>(string s) => new DapperableEnum<TEnum>(s);
}
public class DapperableEnumHandler<TEnum> : SqlMapper.ITypeHandler
where TEnum : Enum
{
public object Parse(Type destinationType, object value)
{
if (destinationType == typeof(DapperableEnum<TEnum>))
{
return new DapperableEnum<TEnum>((string)value);
}
throw new InvalidCastException($"Can't parse string value {value} into enum type {typeof(TEnum).Name}");
}
public void SetValue(IDbDataParameter parameter, object value)
{
parameter.DbType = DbType.String;
parameter.Value =((DapperableEnum<TEnum>)value).Value.GetDescription();
}
}
I use the static constructor to automatically register the type handler at startup.
I use GetDescription / GetValueByDescription (same idea as this answer) to support strings that wouldn't be valid C# enum values. If you don't need this feature, ToString and Enum.Parse will work fine.
The JsonConverter attribute makes Json.Net use string values too. Of course remove it if you don't use Json.Net
Here's an example:
enum Holiday
{
Thanksgiving,
Christmas,
[Description("Martin Luther King, Jr.'s Birthday")]
MlkDay,
Other,
}
class HolidayScheduleItem : IStandardDaoEntity<HolidayScheduleItem>
{
public DapperableEnum<Holiday> Holiday {get; set;}
public DateTime When {get; set;}
}
And calling code can use the normal enum values.
var item = new HolidayScheduleItem()
{
Holiday = Holiday.MlkDay,
When = new DateTime(2021, 1, 18)
};
It works with plain Dapper or Dapper.Contrib:
await conn.ExecuteAsync("INSERT HolidayScheduleItem ([Holiday], [When])
VALUES(@Holiday, @When)", item);
await conn.InsertAsync(item);
I couldn't get the ITypeHandler suggestions to work with enums. However, I was profiling the SQL generated by Dapper and noticed it was declaring the enum parameters as int. So I tried adding a type map for the enum type.
Adding this Dapper config on application startup did the trick for me.
Dapper.SqlMapper.AddTypeMap(typeof(MyEnum), DbType.String);
Then I use connection.Execute(updateSql, model) as normal. Didn't need to use .ToString() or any other explicit conversions. The underlying columns are varchar(20).
Instead of passing in your data object you can pass in a dictionary built off your object w/ the enum converted into a string in the dictionary (so Dapper never sees the Enum)
iow instead of say
connection.Query<MyDataObjectType>(sql, myDataObject);
you can do
connection.Query<MyDataObjectType>(sql, myDataObject.AsDapperParams());
and then have a method like
public static Dictionary<string, object> AsDapperParams(this object o)
{
var properties = o.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(c => c.CanRead).ToArray();
return properties
.Select(c => new {Key = c.Name, Value = c.GetValue(o), Type = c.PropertyType})
.ToDictionary(
c => c.Key,
c => (c.Type.IsEnum || Nullable.GetUnderlyingType(c.Type)
?.IsEnum == true) ? c.Value.ToString() : c.Value);
}
I find this approach works well with DapperExtensions. Setup a Enum field as per normal in your class but then have a 2nd string field for the Enum which represents the value you will be persisting. You can then set the mappings to ignore the Enum field but persist the string value instead. e.g.
// enum field
public Frequency Frequency { get; set;}
// string field of the same enum that you are going to persist
public string DbFrequency
{
get { return this.Frequency.ToString(); }
set { this.Frequency = Enum.Parse<Frequency>(value); }
}
// in your mappings do this
Map(f => f.Frequency).Ignore();
Map(f => f.DbFrequency).Column("Frequency");
It would be to have 2nd string enum as a private member of the class but you have to make it public for this to work AFAIK
