Have Dapper assign DateTimes as UTC, Date as Local

Question

There's a lot of stories around how to have Dapper assign all DateTimes coming back from a query as Utc.

However, my database actually uses the Date data type when we don't care about the time of an event (say, a 'due date'). Unfortunately, this gets converted to a UTC DateTime in C#. This causes some havoc with date picker UIs, which seem to have strange behavior for users across time zones.

Is there a mechanism within Dapper to capture when a Date field is being converted to a DateTime and set the DateTimeKind differently? Or force specific TypeHandler to be used for certain columns?

Answer 1

You can do what you are trying to do but with a caveat. You can create a custom TypeHandler but as the class that you are going to map in C# will be a DateTime dapper already has it's own handling.

On older versions there were no way to replace those (I know by experience, I had to modify dapper as I had a very similar situation) but now you can remove the mapping with SqlMapper.RemoveTypeMap, so you can remove the DateTime mapping and use your custom typehandler.

The caveat: all the DateTimes will be handled by your type handler, you must inside then determine if what you're deserializing is a Date or a DateTime and apply different logic for both, what can be tricky as you receive an object that will be already a DateTime.