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