InvalidCastException when using Dapper to map a Date field from MySql

Question

I am just getting started using Dapper to access a MySql database and I seem to be running into an issue when dealing with date fields. Any time I try to map a MySql Date type field I am receiving an invalid cast. It seems that the MySql connector is returning a MySqlDateTime type which causes Dapper to cast it to an Object instead of a date time. Here's the relevant code

Property definition on my model

public DateTime PickupDate { get; set; }

Field definition in MySql

PickupDate DATE

Exception Being Thrown

System.Data.DataException : Error parsing column 1 (PickupDate= - Object)
----> System.InvalidCastException : Specified cast is not valid.

I know it must be something I'm missing, can anyone point me in the right direction as to what I need to do to have dapper map this field to a DateTime data type?

Answer 1

The answer was to set AllowZeroDateTime to false in the connection string. From the MySql conneciton string options doc found here.

If set to True, MySqlDataReader.GetValue() returns a MySqlDateTime object for date or datetime columns that have disallowed values, such as zero datetime values, and a System.DateTime object for valid values. If set to False (the default setting) it causes a System.DateTime object to be returned for all valid values and an exception to be thrown for disallowed values, such as zero datetime values.

For some reason, even with a valid date, I was getting the MySqlDateTime type instead of DateTime. Changing this to false causes the correct behavior to take place.