InvalidCastException when using Dapper to map a Date field from MySql

.net c# dapper 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?

Accepted Answer

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.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why