Type Handlers and Custom Mapping in Dapper: Guid, DateTimeOffset and CustomPropertyTypeMap

Version note: The code in this post targets Dapper 2.1 and .NET 10.

Guess First

This time our Bookstore database gets a books table that stores the book's identity as a Guid, the publication moment as a DateTimeOffset, the average reading time as a TimeSpan and the first-edition date as a DateOnly. We insert one row and read the same row back:

using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Globalization;
using System.Reflection;
using Dapper;
using Microsoft.Data.Sqlite;

DefaultTypeMap.MatchNamesWithUnderscores = true;

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();

conn.Execute("""
    create table books (
        id text primary key,
        title text not null,
        published_at text not null,
        read_time text not null,
        release_date text not null
    )
    """);

var id = Guid.Parse("3f2b8c1e-7d4a-4e9b-9c61-2a5f0e8d7b13");

conn.Execute(
    "insert into books values (@Id, @Title, @PublishedAt, @ReadTime, @ReleaseDate)",
    new
    {
        Id = id,
        Title = "Dune",
        PublishedAt = new DateTimeOffset(2024, 3, 1, 9, 30, 0, TimeSpan.FromHours(3)),
        ReadTime = TimeSpan.FromMinutes(95),
        ReleaseDate = new DateOnly(1965, 8, 1)
    });

var book = conn.QuerySingle<Book>("select * from books where id = @id", new { id });
Console.WriteLine(book.Title);

public class Book
{
    public Guid Id { get; set; }
    public string Title { get; set; } = "";
    public DateTimeOffset PublishedAt { get; set; }
    public TimeSpan ReadTime { get; set; }
    public DateOnly ReleaseDate { get; set; }
}

There are three candidates: the insert blows up, where id = @id finds no rows, or everything works and the screen says Dune. Make your guess, then look at the output:

Unhandled exception. System.Data.DataException: Error parsing column 0 (id=3F2B8C1E-7D4A-4E9B-9C61-2A5F0E8D7B13 - String)
 ---> System.InvalidCastException: Invalid cast from 'System.String' to 'System.Guid'.

A fourth option won. The insert ran fine and the where found the row, but Dapper tripped over the very first column while turning the row into a Book object. Writing is easy, reading is hard. This post is about that asymmetry and the tool that closes it: type handlers.

SQLite's Four Boxes

Let's look at how the row actually sits in the database. typeof() tells you the real storage class SQLite keeps for each value:

var row = conn.QuerySingle("""
    select id, typeof(id) as id_type, published_at, read_time, release_date
    from books
    """);
Console.WriteLine(row);

{DapperRow, id = '3F2B8C1E-7D4A-4E9B-9C61-2A5F0E8D7B13', id_type = 'text', published_at = '2024-03-01 09:30:00+03:00', read_time = '01:35:00', release_date = '1965-08-01'}

SQLite only has four boxes: integer, real, text and blob. Microsoft.Data.Sqlite is smart when it puts parameters into those boxes: it turns a Guid into upper-case text, a DateTimeOffset into a date string with an offset and a TimeSpan into 01:35:00. In the write direction the provider already does the work.

In the read direction, Dapper uses the reader's indexer, and the provider only ever returns long, double, string or byte[] from it. To put a string into a Guid property, Dapper falls back to Convert.ChangeType; there is no such conversion path for Guid, DateTimeOffset or TimeSpan, and the result is the exception above. The "Dapper limitations" page on Microsoft Learn names exactly these three types. ReleaseDate is not on the list; we will get to why it works in a moment.

TypeHandler<T>: A Two-Way Interpreter

SqlMapper.TypeHandler<T> is a small class with two abstract methods: Parse turns the value coming from the database into T, and SetValue puts a T into an IDbDataParameter. Think of an interpreter: one hand reads the incoming letter, the other writes the outgoing one. On SQLite the provider already writes the outgoing letter correctly, so writing the reading side is enough:

public abstract class SqliteTypeHandler<T> : SqlMapper.TypeHandler<T>
{
    // Microsoft.Data.Sqlite already converts the parameter
    public override void SetValue(IDbDataParameter parameter, T? value) => parameter.Value = value;
}

public sealed class GuidHandler : SqliteTypeHandler<Guid>
{
    public override Guid Parse(object value) => Guid.Parse((string)value);
}

public sealed class DateTimeOffsetHandler : SqliteTypeHandler<DateTimeOffset>
{
    public override DateTimeOffset Parse(object value) =>
        DateTimeOffset.Parse((string)value, CultureInfo.InvariantCulture);
}

public sealed class TimeSpanHandler : SqliteTypeHandler<TimeSpan>
{
    public override TimeSpan Parse(object value) =>
        TimeSpan.Parse((string)value, CultureInfo.InvariantCulture);
}

This pattern matches Microsoft's own sample; I only added CultureInfo.InvariantCulture, because date parsing invites surprises when the server's regional setting is something like tr-TR. We register the handlers once, at application startup:

SqlMapper.AddTypeHandler(new GuidHandler());
SqlMapper.AddTypeHandler(new DateTimeOffsetHandler());
SqlMapper.AddTypeHandler(new TimeSpanHandler());

var book = conn.QuerySingle<Book>("select * from books where id = @id", new { id });
Console.WriteLine($"{book.Id} | {book.PublishedAt:O} | {book.ReadTime} | {book.ReleaseDate:yyyy-MM-dd}");

3f2b8c1e-7d4a-4e9b-9c61-2a5f0e8d7b13 | 2024-03-01T09:30:00.0000000+03:00 | 01:35:00 | 1965-08-01

Nullable types do not need a separate handler. When AddTypeHandler registers Guid, it also wires Guid? to the same handler, and when the column comes back NULL, Parse is never called; the property simply becomes null:

SqlMapper.AddTypeHandler(new GuidHandler());

conn.Execute("create table reviews (id integer primary key, book_id text not null, reviewer_id text)");
conn.Execute("insert into reviews values (1, @BookId, null)", new { BookId = id });

var review = conn.QuerySingle<Review>("select * from reviews");
Console.WriteLine($"{review.BookId} | {review.ReviewerId?.ToString() ?? "null"}");

public class Review
{
    public long Id { get; set; }
    public Guid BookId { get; set; }
    public Guid? ReviewerId { get; set; }
}

3f2b8c1e-7d4a-4e9b-9c61-2a5f0e8d7b13 | null

Why Was SetValue Never Called?

Imagine you think, "the interpreter works both ways, so let me write Guids in lower case." We write a handler that fills in SetValue and put a log line inside it so we can see it run:

public sealed class LowerGuidHandler : SqlMapper.TypeHandler<Guid>
{
    public override Guid Parse(object value) => Guid.Parse((string)value);

    public override void SetValue(IDbDataParameter parameter, Guid value)
    {
        Console.WriteLine("SetValue called");
        parameter.DbType = DbType.String;
        parameter.Value = value.ToString();
    }
}

conn.Execute("create table tags (id text primary key, name text not null)");
SqlMapper.AddTypeHandler(new LowerGuidHandler());

conn.Execute("insert into tags values (@Id, 'sci-fi')", new { Id = id });
Console.WriteLine(conn.ExecuteScalar<string>("select id from tags"));

3F2B8C1E-7D4A-4E9B-9C61-2A5F0E8D7B13

No log line and no lower case. The reason is in Dapper's source, in the LookupDbType method that decides the parameter type: Dapper first checks its own built-in type table and only moves on to handlers if it finds nothing there. Guid, DateTimeOffset and TimeSpan are registered in that table, so the handler takes part on the read side and is silently skipped on the write side.

Remove the type from that table and SetValue starts running, but now a different hole opens:

conn.Execute("create table tags (id text primary key, name text not null)");
SqlMapper.RemoveTypeMap(typeof(Guid));
SqlMapper.AddTypeHandler(new LowerGuidHandler());

conn.Execute("insert into tags values (@Id, 'sci-fi')", new { Id = id });
Console.WriteLine(conn.ExecuteScalar<string>("select id from tags"));

var hits = conn.ExecuteScalar<long>(
    "select count(*) from tags where id in @ids", new { ids = new[] { id } });
Console.WriteLine(hits);

SetValue called
3f2b8c1e-7d4a-4e9b-9c61-2a5f0e8d7b13
0

The row was written in lower case, yet the in @ids query could not find it. List expansion puts each element into a parameter as is and does not use the handler, and the provider sends the Guid in upper case again. Text comparison in SQLite is case-sensitive, so the result is zero. The lesson: on SQLite, leave the write format to the provider and use the handler for reading. If you must own the format, make sure every path the data takes (single parameters, lists, raw SQL) follows the same rule.

The Second Route: The Provider's Own Reader

Since Dapper 2.0.138, AddTypeMap has an overload with a third parameter. With useGetFieldValue: true, Dapper reads the value with the provider's GetFieldValue<T>() method instead of the indexer. Microsoft.Data.Sqlite knows how to produce Guid, DateTimeOffset and TimeSpan from text in that method:

SqlMapper.AddTypeMap(typeof(Guid), DbType.Guid, useGetFieldValue: true);
SqlMapper.AddTypeMap(typeof(DateTimeOffset), DbType.DateTimeOffset, useGetFieldValue: true);
SqlMapper.AddTypeMap(typeof(TimeSpan), DbType.Time, useGetFieldValue: true);

var book = conn.QuerySingle<Book>("select * from books where id = @id", new { id });
Console.WriteLine($"{book.Id} | {book.PublishedAt:O} | {book.ReadTime}");

3f2b8c1e-7d4a-4e9b-9c61-2a5f0e8d7b13 | 2024-03-01T09:30:00.0000000+03:00 | 01:35:00

The difference between the two routes is clear. The handler route is provider-independent: you decode whatever format you want inside Parse and can tolerate oddities in old data right there. The GetFieldValue route works with zero classes but depends entirely on what the provider knows about that type; switch providers and the behavior may change. For Bookstore I prefer handlers, because they make the rule visible in code.

DateOnly and TimeOnly: The End of a Long Story

Why did ReleaseDate cause no trouble in the first example? Because as of Dapper 2.1.86, Dapper recognizes DateOnly and TimeOnly and reads them with the provider's GetFieldValue<T>(). The story is a bumpy one: support first arrived in 2.1.37, turned out to be broken, was reverted in 2.1.42, and the packages from 2.1.37 to 2.1.44 were unlisted on NuGet. The fixed version came back with 2.1.86 in September 2026. On an older version you had to write handlers for these two types as well.

conn.Execute("create table events (id integer primary key, day text not null, starts_at text not null)");
conn.Execute(
    "insert into events values (1, @Day, @StartsAt)",
    new { Day = new DateOnly(2026, 11, 6), StartsAt = new TimeOnly(19, 30) });

Console.WriteLine(conn.QuerySingle("select day, starts_at from events"));

var ev = conn.QuerySingle<BookEvent>("select * from events");
Console.WriteLine($"{ev.Day:yyyy-MM-dd} {ev.StartsAt:HH:mm}");

public class BookEvent
{
    public long Id { get; set; }
    public DateOnly Day { get; set; }
    public TimeOnly StartsAt { get; set; }
}

{DapperRow, day = '2026-11-06', starts_at = '19:30:00'}
2026-11-06 19:30

SQL Server and PostgreSQL are covered too: Dapper converts to DateOnly even when the provider returns a DateTime for a date column, and to TimeOnly even when it returns a TimeSpan for time.

When Column Names Don't Match: CustomPropertyTypeMap

A type handler tells Dapper how to convert a value; it does not say which column goes to which property. That is the type map's job. Someone coming from EF Core reaches for the [Column] attribute first, but core Dapper does not know that attribute:

conn.Execute("create table editions (id integer primary key, book_title text, pub_year integer)");
conn.Execute("insert into editions values (1, 'Dune', 1965)");

var before = conn.QuerySingle<Edition>("select * from editions");
Console.WriteLine($"{before.Id} | '{before.Title}' | {before.PublishedYear}");

public class Edition
{
    public long Id { get; set; }

    [Column("book_title")]
    public string Title { get; set; } = "";

    [Column("pub_year")]
    public int PublishedYear { get; set; }
}

1 | '' | 0

No error, just empty values, and being silent makes it the most dangerous kind. Even with MatchNamesWithUnderscores turned on, pub_year only goes to PubYear. The fix is to describe the mapping yourself: CustomPropertyTypeMap takes a function that is asked, for each column name, which property to pick. Reading the attribute is entirely your decision:

SqlMapper.SetTypeMap(typeof(Edition), new CustomPropertyTypeMap(
    typeof(Edition),
    (type, columnName) => type.GetProperties().FirstOrDefault(p =>
        string.Equals(p.GetCustomAttribute<ColumnAttribute>()?.Name, columnName, StringComparison.OrdinalIgnoreCase)
        || string.Equals(p.Name, columnName, StringComparison.OrdinalIgnoreCase))!));

var after = conn.QuerySingle<Edition>("select * from editions");
Console.WriteLine($"{after.Id} | '{after.Title}' | {after.PublishedYear}");

1 | 'Dune' | 1965

When the function returns null, that column is skipped. There is one limitation to know: CustomPropertyTypeMap only works with a parameterless constructor. Apply the same mapping to a record:

SqlMapper.SetTypeMap(typeof(EditionRecord), new CustomPropertyTypeMap(
    typeof(EditionRecord),
    (type, columnName) => type.GetProperties().FirstOrDefault(p =>
        string.Equals(p.Name, columnName, StringComparison.OrdinalIgnoreCase))!));

var edition = conn.QuerySingle<EditionRecord>(
    "select id, book_title as title, pub_year as published_year from editions");

public record EditionRecord(long Id, string Title, int PublishedYear);

Unhandled exception. System.InvalidOperationException: A parameterless default constructor or one matching signature (System.Int64 id, System.String title, System.Int64 published_year) is required for EditionRecord materialization

If you work with records, the cleanest route is an alias in SQL, or leaning on MatchNamesWithUnderscores and constructor mapping from post 02. Compared with EF Core's per-property value converters and column configuration, both handlers and type maps in Dapper are per type and process-wide; reading this side by side with value conversion in post 20 of the EF Core series shows the difference well.

Common Pitfalls

Registering a handler after the first query. Dapper caches the deserializer it builds for each query, and AddTypeHandler does not clear that cache:

Try(() => conn.QuerySingle<Book>("select * from books"));

SqlMapper.AddTypeHandler(new GuidHandler());
SqlMapper.AddTypeHandler(new DateTimeOffsetHandler());
SqlMapper.AddTypeHandler(new TimeSpanHandler());
Try(() => conn.QuerySingle<Book>("select * from books"));

SqlMapper.PurgeQueryCache();
Console.WriteLine(conn.QuerySingle<Book>("select * from books").Id);

static void Try(Action action)
{
    try { action(); }
    catch (Exception ex) { Console.WriteLine(ex.Message); }
}

Error parsing column 0 (id=3F2B8C1E-7D4A-4E9B-9C61-2A5F0E8D7B13 - String)
Error parsing column 0 (id=3F2B8C1E-7D4A-4E9B-9C61-2A5F0E8D7B13 - String)
3f2b8c1e-7d4a-4e9b-9c61-2a5f0e8d7b13

The first two calls failed with the same error; the second one used the old deserializer even though the handlers were registered. Put registrations at the very top of Program.cs, before the first query. PurgeQueryCache is a rescue tool, not a design.

Writing a handler for an enum and expecting it to be used for parameters. By default Dapper sends enums as their underlying numeric type; to bring the handler's SetValue into play you need Settings.PreferTypeHandlersForEnums, added in 2.1.79:

conn.Execute("create table formats (id integer primary key, format text not null)");
SqlMapper.AddTypeHandler(new BookFormatHandler());

conn.Execute("insert into formats values (1, @Format)", new { Format = BookFormat.Paperback });

SqlMapper.Settings.PreferTypeHandlersForEnums = true;
conn.Execute("insert into formats values (2, @Format)", new { Format = BookFormat.Paperback });

foreach (var r in conn.Query("select id, format from formats"))
    Console.WriteLine(r);

public enum BookFormat { Hardcover, Paperback, Ebook }

public sealed class BookFormatHandler : SqlMapper.TypeHandler<BookFormat>
{
    public override BookFormat Parse(object value) =>
        Enum.Parse<BookFormat>((string)value, ignoreCase: true);

    public override void SetValue(IDbDataParameter parameter, BookFormat value)
    {
        parameter.DbType = DbType.String;
        parameter.Value = value.ToString().ToLowerInvariant();
    }
}

{DapperRow, id = '1', format = '1'}
{DapperRow, id = '2', format = 'paperback'}

Forgetting that registrations are global. AddTypeHandler, SetTypeMap and Settings are process-wide static state. Tests running in parallel can overwrite each other's registrations, and in an application that talks to two different databases the same Guid handler runs for both providers. Finally, Dapper.AOT does not see these runtime registrations; you declare handlers with an attribute instead, as we will see in post 16.

A Small Experiment

★☆☆ Add a text column named price to the books table and write prices like "12.50". Before writing a SqliteTypeHandler<decimal> for a decimal Price property, observe what the query does, then add the handler and compare.

★★☆ Run the LowerGuidHandler example with RemoveTypeMap and fix the in @ids query returning zero. Try two routes: sending the list as a lower-case string[], and using lower(id) in SQL. Check with explain query plan which one keeps using the index.

★★★ Write your own [DbColumn] attribute and a helper that, at startup, scans every type in the assembly and registers a CustomPropertyTypeMap for each type that carries the attribute. Don't forget to fall back to default name matching for properties without the attribute.

Handlers and type maps showed how Dapper makes peace with SQLite's limited type world. In the next post we leave SQLite for a while and move to SQL Server: stored procedures, output and return value parameters, and the trap set by the automatic stored procedure detection that arrived in 2.1.11.