尝试设置从MySQL返回的布尔值时，Dapper抛出无效的强制转换异常 [英] Dapper is throwing an invalid cast exception when trying to set a boolean value returned from MySQL

问题描述

我有这堂课

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

然后我使用dapper使用此sql填充它:

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:

错误解析第2列(IsValidated = 1-Int64)

Error parsing column 2 (IsValidated=1 - Int64)

我已经完成了精巧的代码& sqldatareader表示该列为int64，因此.NET Mysql Connector似乎认为'TRUE'(在MYSQL中应为tinyint)是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.

我确实找到了此错误报告，其中指出对于INT的所有版本(INT，BIGINT， TINYINT，SMALLINT，MEDIUMINT..NET连接器返回int64.但是，这是MySQL 5.0及更高版本中的错误.是固定的，我正在使用5.5.我有mysql.data版本6.4.3.0

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

我已通过将所有内容全部选择为声明为BOOL的IsValidated列的临时表来解决"此问题，但这是一个糟糕的解决方案.

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.

推荐答案

我不熟悉Drapper，但是由于MySQL会将布尔值返回为int类型(通常为tinyint)，因此一种选择是将您的类更改为以下:

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); }
   }  
}  

或者尝试在sql中进行强制转换

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.

这篇关于尝试设置从MySQL返回的布尔值时，Dapper抛出无效的强制转换异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！