How to resolve the Dapper System.Data.DataException HResult=0x80131501 InvalidCastException Invalid cast from 'System.String' to 'System.Uri'

Recently, I refactored some code which resulted in changing the type of some properties of an entity from System.String to System.URI. The names of the properties in question contained the substring URI or URL and the SonarLint static code analyzer was recommending that the code be refactored to use the System.URI type instead of System.String for those properties, which makes sense in our solution.

In this project, we are leveraging StackExchange's Dapper as an ultra fast and light object mapper, but after the refactoring, I started receiving this error when trying to retrieve data from my repository:

System.Data.DataException
  HResult=0x80131501
  Message=Error parsing column 5 (RequestUrl=https://www.myurl.com/api/wow - String)
  Source=Dapper
  StackTrace:
   at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in C:\projects\dapper\Dapper\SqlMapper.cs:line 3609
   at Dapper.SqlMapper.<>c__DisplayClass156_0`8.<GenerateMapper>b__1(IDataReader r) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1544
   at Dapper.SqlMapper.<MultiMapImpl>d__153`8.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.cs:line 1444
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.<MultiMapAsync>d__52`8.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.Async.cs:line 949
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at My.Namespace.MyRepository.<GetAllRepositoryMethod>d__2.MoveNext() in C:\dev\repos\myproject\MyRepository.cs:line 59
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at My.Namespace.Program.CallerMethod(Object sender, ElapsedEventArgs e) in C:\dev\repos\myproject\Program.cs:line 109
   at System.Timers.Timer.MyTimerCallback(Object state)

Inner Exception 1:
InvalidCastException: Invalid cast from 'System.String' to 'System.Uri'.

How can I get around this error?

It turns out that Dapper is easily extensible and by declaring a simple class that extends SqlMapper.TypeHandler<T>, you can define a mapping (TypeHandler) between the System.String and System.URI types and enable Dapper to perform the cast.

Here's what that class looks like:

using System;
using System.Data;
using Dapper;

namespace My.Namespace.Data.DatabaseTools
{
    public class DapperUriTypeHandler : SqlMapper.TypeHandler<Uri>
    {
        public override void SetValue(IDbDataParameter parameter, Uri value)
        {
            parameter.Value = value.ToString();
        }

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

Once you have declared the mapping, you can let Dapper know about it by calling the SqlMapper.AddTypeHandler method and providing an instance of your custom type handler class as a parameter like so:

SqlMapper.AddTypeHandler(new DapperUriTypeHandler());

The best place to call SqlMapper.AddTypeHandler is somewhere in your startup configuration so that the type handler is known to Dapper early on in the execution of your application or service.

I Hope this can help other community members resolve similar issues with Dapper and type casting quickly in the future!