Get DateTime as UTC with Dapper

Question

I'm using Dapper to map my entities to SQL Server CE. If I save a DateTime with Kind=Utc, when I read it back I get a DateTime with Kind=Unspecified, which leads to all kind of problems.

Example:

var f = new Foo { Id = 42, ModificationDate = DateTime.UtcNow };
Console.WriteLine("{0} ({1})", f.ModificationDate, f.ModificationDate.Kind);
connection.Execute("insert into Foo(Id, ModificationDate) values(@Id, @ModificationDate)", f);
var f2 = connection.Query<Foo>("select * from Foo where Id = @Id", f).Single();
Console.WriteLine("{0} ({1})", f2.ModificationDate, f2.ModificationDate.Kind);

This code gives the following output:

20/09/2012 10:04:16 (Utc)
20/09/2012 10:04:16 (Unspecified)

I know I should be using a DateTimeOffset, but unfortunately SQL CE has no support for this type.

Is there a workaround? Can I tell Dapper to assume that all dates have DateTimeKind.Utc? And more generally, what are my options to customize the mapping?

---

EDIT: My current workaround is to patch the dates after Dapper has materialized the result, but it kind of smells...

var results = _connection.Query<Foo>(sql, param).Select(PatchDate);

...

static Foo PatchDate(Foo f)
{
    if (f.ModificationDate.Kind == DateTimeKind.Unspecified)
        f.ModificationDate = DateTime.SpecifyKind(f.ModificationDate, DateTimeKind.Utc);
    return f;
}

Answer 1

Adding this answer for anyone else who comes looking for a simple fix. This is possible now with the addition of SqlMapper.TypeHandler in Dapper.

Add this class to convert the value from the db to a datetime with the kind specified as UTC.

public class DateTimeHandler : SqlMapper.TypeHandler<DateTime>
{
    public override void SetValue(IDbDataParameter parameter, DateTime value)
    {
        parameter.Value = value;
    }

    public override DateTime Parse(object value)
    {
        return DateTime.SpecifyKind((DateTime)value, DateTimeKind.Utc);
    }
}

Then in my Global.asax file of my Web API I add the type handler to dapper.

SqlMapper.AddTypeHandler(new DateTimeHandler());

If you need to ensure you are always inserting dates as UTC, then on the SetValue method you can use:

parameter.Value = DateTime.SpecifyKind(value, DateTimeKind.Utc);

Answer 2

Looked into the Dapper code. Unless mine was out of date, for value types like datetime (which is mapped to DbType.DateTime), dapper just does a simple cast from the IDataReader object.

Pseudo : yield return (DateTime)IDataReader.GetValue(0);

That's the specific case for Datetime out of a bunch of generic code and lambdas.

AFAIK, SQL datetime never stores the offset / timezone so the kind will always say "Unspecified" on any datetime you store and fetch.

So, to do it cleanly, you could touch dapper internals:

which is a pain as you'd have to touch a big IL generating method (the DataRow Deserializer) and put in an if case for DateTime.

OR

just put a setter on the DateTime props where UTC is an issue (which is kinda against POCO but is relatively sane):

class Foo
{
    private DateTime _modificationDate;
    public DateTime ModificationDate
    {
        get { return _modificationDate; }
        set { _modificationDate = DateTime.SpecifyKind(value, DateTimeKind.Utc); }
    }
    //Ifs optional? since it's always going to be a UTC date, and any DB call will return unspecified anyways
}

Answer 3

Just wanted to put my full solution here for seamlessly integrating DateTimeOffset / DateTimeOffset? fields/properties with a MySQL 5.7 database (which doesn't support DbType.DateTimeOffset) - based on @matt-jenkins answer above:

public static class DapperExtensions
{
    class DateTimeOffsetTypeHandler : SqlMapper.TypeHandler<DateTimeOffset>
    {
        public override void SetValue(IDbDataParameter parameter, DateTimeOffset value)
        {
            switch (parameter.DbType)
            {
                case DbType.DateTime:
                case DbType.DateTime2:
                case DbType.AnsiString: // Seems to be some MySQL type mapping here
                    parameter.Value = value.UtcDateTime;
                    break;
                case DbType.DateTimeOffset:
                    parameter.Value = value;
                    break;
                default:
                    throw new InvalidOperationException("DateTimeOffset must be assigned to a DbType.DateTime SQL field.");
            }
        }

        public override DateTimeOffset Parse(object value)
        {
            switch (value)
            {
                case DateTime time:
                    return new DateTimeOffset(DateTime.SpecifyKind(time, DateTimeKind.Utc), TimeSpan.Zero);
                case DateTimeOffset dto:
                    return dto;
                default:
                    throw new InvalidOperationException("Must be DateTime or DateTimeOffset object to be mapped.");
            }
        }
    }


    private static int DateTimeOffsetMapperInstalled = 0;

    public static void InstallDateTimeOffsetMapper()
    {
        // Assumes SqlMapper.ResetTypeHandlers() is never called.
        if (Interlocked.CompareExchange(ref DateTimeOffsetMapperInstalled, 1, 0) == 0)
        {
            // First remove the default type map between typeof(DateTimeOffset) => DbType.DateTimeOffset (not valid for MySQL)
            SqlMapper.RemoveTypeMap(typeof(DateTimeOffset));
            SqlMapper.RemoveTypeMap(typeof(DateTimeOffset?));

            // This handles nullable value types automatically e.g. DateTimeOffset?
            SqlMapper.AddTypeHandler(typeof(DateTimeOffset), new DateTimeOffsetTypeHandler());
        }
    }
}

Answer 4

If you are using Dapper from source (not nuget), you could tweak the code to always force DateTimeKind of UTC. A more configurable option might be to create a new attribute for DateTime property values that allow you to specify date time kind as a hint to dapper. Dapper could look for DateTime properties with this attribute and when found could use it to specify the DateTime kind during ORM mapping. This might be a nice feature for core dapper as you are not the only one with this issue :)