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?

Hmmm... That's vexing. I could try changing it to use GetDateTime explicitly; are zero-dates going to be a problem? (it depends also on the ZeroDateTimeBehavior in the connection-string) — Marc Gravell
After reading your comment, I change the allow zero date time option in the connection string to false and it now works as expected. Reading the documentation at dev.mysql.com/doc/refman/5.5/en/… though it seems that even if it is true, it should return a DateTime type for valid dates, but I'm getting the exception instead... — Mickey
so is it sorted then? If so, since you found it, do you want to add an answer talking about AllowZeroDateTime for the benefit of the next reader? If it isn't, can you clarify the scenario where it fails? — Marc Gravell
This takes care of me, but it still does not seem to work as expected, although I think the problem is more related to the way the mysql connector handles it. What would be nice is if there was some way to extend it so I could have it convert MySqlDateTime types to a nullable DateTime type instead of it just returning it as an object. None the less, this did solve my issue so I added the answer. — Mickey

Mickey Mickey · Accepted Answer · 2012-07-30T13:32:30

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.

InvalidCastException when using Dapper to map a Date field from MySql

1 Answers