Thomas Levesque Thomas Levesque - 2 months ago 19
C# Question

Get DateTime as UTC with Dapper

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

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
}
Comments