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

dapper mysql mysql.data

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.

Popular Answer

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.




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