Dapper TypeHandler.SetValue() not being called

Question

I am testing Dapper to load / persist objects to an Oracle database, and to manage Oracle's Guid storage I need a SqlMapper.TypeHandler<Guid>. When loading a Guid column from the database the Parse method is called, but when I attempt to execute an SQL statement using a Guid parameter I get the following exception:

System.ArgumentException was unhandled; Message=Value does not fall within the expected range.Source=Oracle.DataAccess.

In debug I can see that my handler's Parse() method is being called when loading my class from the database, but the SetValue() mdethod is not.

The code to reproduce the exception is below

---

CREATE TABLE foo (id     RAW (16) NOT NULL PRIMARY KEY,
                  name   VARCHAR2 (30) NOT NULL);

INSERT INTO foo (id, name) VALUES (SYS_GUID (), 'Bar');

COMMIT;

---

using System;
using System.Linq;
using Dapper;
using Oracle.DataAccess.Client;

namespace Program
{
    public class Foo
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
    }

    class GuidTypeHandler : SqlMapper.TypeHandler<Guid>
    {
        public override Guid Parse(object value)
        {
            Console.WriteLine("Handling Parse of {0}", value);

            var inVal = (byte[])value;
            byte[] outVal = new byte[] { inVal[3], inVal[2], inVal[1], inVal[0], inVal[5], inVal[4], inVal[7], inVal[6], inVal[8], inVal[9], inVal[10], inVal[11], inVal[12], inVal[13], inVal[14], inVal[15] };
            return new Guid(outVal);
        }

        public override void SetValue(System.Data.IDbDataParameter parameter, Guid value)
        {
            Console.WriteLine("Handling Setvalue of {0}", value);

            var inVal = value.ToByteArray();
            byte[] outVal = new byte[] { inVal[3], inVal[2], inVal[1], inVal[0], inVal[5], inVal[4], inVal[7], inVal[6], inVal[8], inVal[9], inVal[10], inVal[11], inVal[12], inVal[13], inVal[14], inVal[15] };
            parameter.Value = outVal;
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            SqlMapper.AddTypeHandler<Guid>(new GuidTypeHandler());
            var conn = new OracleConnection(Resources.ConnectionString);
            var def = new CommandDefinition("select id, name from foo");

            conn.Open();

            var foo = conn.Query<Foo>(def).First();
            Console.WriteLine(foo.Id + "; " + foo.Name);

            foo.Name = "New Bar";

            def = new CommandDefinition(
                "UPDATE foo SET name = :name WHERE id = :id",
                parameters: new { ID = foo.Id, NAME = foo.Name });

            var rows = conn.Execute(def);
            Console.WriteLine("{0} rows inserted", rows);

            Console.ReadLine();
        }
    }
}

Answer 1

In case anyone else stumbles upon this post with a similar problem, I found a solution to handling Guids without the need for wrappers.

The problem in Dapper is the order in which Dapper searches for matching DbType and TypeHandler implementations. Dapper prefers "native" DbType for a Guid (in SqlMapper#LookupDbType). In order to make Dapper use your own implementation, you have to remove the default mapping in addition to adding your own TypeHandler:

SqlMapper.AddTypeHandler<Guid>(new GuidTypeHandler());
SqlMapper.RemoveTypeMap(typeof(Guid));
SqlMapper.RemoveTypeMap(typeof(Guid?));

I currently use a string-based implementation when working with SQLite:

public class GuidAsStringHandler : SqlMapper.TypeHandler<Guid>
{
    public override Guid Parse(object value)
    {
        return new Guid((string) value);
    }

    public override void SetValue(IDbDataParameter parameter, Guid value)
    {
        parameter.Value = value.ToString();
    }
}