Map string to guid with Dapper

It looks like there is UUID type in PostgreSQL already. But @Cpt.Ohlund's solution is still great for MySQL/MariaDB in 2020.

But the solution might cause problems itself.

When VARCHAR(36) is used for System.Guid the following exception is thrown:

System.InvalidCastException: Invalid cast from 'System.String' to 'System.Guid'.

@Cpt.Ohlund's solution makes it work.

But if the column is CHAR(36) then it is mapped to System.Guid automatically! And if @Cpt.Ohlund's solution is applied there will be another exception:

System.InvalidCastException: Unable to cast object of type 'System.Guid' to type 'System.String'.

The exception is caused by an instance System.Guid passed to Parse(object value) instead of a string.

---

So the simple answer is to use CHAR(36) in MySQL and MariaDB and it will just work.

But if you need to handle any string type of the column you have to use an improved @Cpt.Ohlund's solution:

public class MySqlGuidTypeHandler : SqlMapper.TypeHandler<Guid>
{
    public override void SetValue(IDbDataParameter parameter, Guid guid)
    {
        parameter.Value = guid.ToString();
    }

    public override Guid Parse(object value)
    {
        // Dapper may pass a Guid instead of a string
        if (value is Guid)
            return (Guid)value;

        return new Guid((string)value);
    }
}

And register it using SqlMapper.AddTypeHandler().

Perhaps the simplest way to do this (without waiting on dapper) is to have a second property:

public Guid Foo {get;set;}

public string FooString {
    get { return Foo.ToString("N"); }
    set { Foo = new Guid(value); }
}

And in your query, alias the column as FooString.

Of course, then this prompts the question: should dapper support private properties for this type of thing? To which I say: probably.

I'm using MySql but it has the same problem since I store the Guid as a string. To fix the mapping without having to alias the column i used the following:

public class MySqlGuidTypeHandler : SqlMapper.TypeHandler<Guid>
{
    public override void SetValue(IDbDataParameter parameter, Guid guid)
    {
        parameter.Value = guid.ToString();
    }

    public override Guid Parse(object value)
    {
        return new Guid((string)value);
    }
}

And in my Startup.cs:

public void ConfigureServices(IServiceCollection services)
    {
        SqlMapper.AddTypeHandler(new MySqlGuidTypeHandler());
        SqlMapper.RemoveTypeMap(typeof(Guid));
        SqlMapper.RemoveTypeMap(typeof(Guid?));
    }