Get DateTime as UTC with Dapper

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);

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
}