Dapper and Enums as Strings

Question

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..

Answer 1

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.