11

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.

Glenn Slaven
  • 33,720
  • 26
  • 113
  • 165
  • 2
    I have always felt uncomfortable about downcasting, you risk losing information. We *may* add extensiblity hooks in Dapper so you can define downcasting rules ... undecided on that at the moment – Sam Saffron Sep 15 '11 at 08:22
  • But this isn't downcasting is it? MySQL is returning a tinyint (TRUE) – Glenn Slaven Sep 16 '11 at 00:27
  • 1
    This problems happens as well with System.Data.SQLite. Dapper only accepts INTEGER data type as beeing mapped to Int64. – Khalid Salomão Jan 13 '12 at 21:05

1 Answers1

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.

jornare
  • 2,903
  • 19
  • 27