Get DateTime as UTC with Dapper
Solution 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);
Solution 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
}
Solution 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());
}
}
}
Thomas Levesque
I'm a French C# developer from Paris. I work mostly on ASP.NET Core projects. I'm a Microsoft MVP (Development technologies) since 2012.
Updated on July 08, 2022Comments
-
Thomas Levesque almost 2 years
I'm using Dapper to map my entities to SQL Server CE. If I save a
DateTime
withKind=Utc
, when I read it back I get aDateTime
withKind=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; }