Search code examples
mysqldatetimedapper

Dapper returns 01-01-0001 00:00:00 from MySQL DATETIME

I can't get Dapper to work together with MySQL database DATETIME type. It always returns "01-01-0001 00:00:00" for DATETIME columns. Here are some details:

.NET: 
DNX 4.5.1
MysqlData 6.9.8
Dapper - 1.50.0-beta6
MySQL: Server Version: 5.5.43-0+deb8u1

Table:

CREATE TABLE campaign (
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  ...
  creation_date datetime DEFAULT NULL,
  last_modified datetime DEFAULT NULL,
  ...
)
ENGINE = INNODB

C#:

public class Campaign 
 {
     public int Id { get; set; }
     public DateTime CreationDate {get; set; }
     public DateTime LastModified { get; set; }
     public DateTime StartDate { get; set; }
     public DateTime EndDate { get; set; }
 }


...
const string myConnectionString = "server=[IP];uid=[usr];pwd=[pass];database=[db];AllowZeroDatetime=false;";
 try
 {
     _conn = new MySqlConnection { ConnectionString = myConnectionString };
     _conn.Open();
 }
...

public Dictionary<int, Campaign> GetCampaigns()
{
            var campaignsDict = _conn.Query<Campaign>("select * from campaign").ToDictionary(row => row.Id,row => row);
            return campaignsDict;
}

that's how one retrieved row looks inside the campaignsDict:

enter image description here

P.S. I'm just learning .NET, so I'm a noob in this technology.

Solution

  • Dapper does not automatically remove underscores unless told to do so, via:

    Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

    So what you actually have here is properties that haven't been mapped from columns. The default value of a DateTime is: 01-01-0001 00:00:00.

    Options:

    • enable underscore matching
    • alias the columns in the query to match the properties
    • change the properties to match the database columns
    • write a custom type map