Dapper is throwing an invalid cast exception when trying to set a boolean value returned from MySQL

Question

I have this class

public class User
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public bool IsValidated { get; set; }
}

And I'm populating it with this sql using dapper:

var users = connection.Query<User>("SELECT userId, userName, TRUE `IsValidated` FROM user WHERE [...]").ToList();

When I run this I get this error:

Error parsing column 2 (IsValidated=1 - Int64)

I've stepped through the dapper code & the sqldatareader is saying that that column is int64, so it looks like the .NET Mysql Connector is thinking that 'TRUE' (which should be tinyint in MYSQL) is an int64.

I did find this bug report which said that for all versions of INT (INT, BIGINT, TINYINT, SMALLINT,MEDIUMINT) the .NET connector was returning int64. However this was a bug in MySQL 5.0 & was fixed, I'm using 5.5. I've got mysql.data version 6.4.3.0

I have "solved" this problem by selecting it all into a temporary table with the IsValidated column declared as BOOL, but this is a lousy solution.

Answer 1

I'm not familiar with Drapper, but since MySQL will return any boolean as an int (normally tinyint), one option could be to change your class to the following:

public class User  
{  
    public int UserId { get; set; }  
    public string UserName { get; set; }
    private bool _isValidated = false;
    public bool IsValidated
    { 
        get{ return _isValidated;}
        set{ _isValidated = Boolean.Parse(value); }
   }  
}  

Alternatively try a cast in the sql

cast(TRUE `IsValidated` as bit)

I haven't tried this, but at least you have a suggestion. All the best.