Dapper extensions throws Specified cast is not valid when insert method is executed in MySQL database

c# dapper mysql

Question

There is a user account table with three columns: ID(int, not null), UserName(varchar(20)) and Email(varchar(50)). The column ID is primary key and with auto_increment constraint. There is a entity class AccountInfo mapped to the table. When I call insert method, it will throw 'Specified cast is not valid.' lined 879 in QueryInternal method. But if I remove the property ID from entity AccountInfo, it works fine. Could anyone make any suggestions? Thanks.

Some codes are here:

1) Insert method

public dynamic Insert<T>(IDbConnection conn, T entity, IDbTransaction transaction = null) where T : class
{
    dynamic result = conn.Insert<T>(entity, transaction);
    return result;
}



public dynamic Insert<T>(IDbConnection connection, T entity, IDbTransaction transaction, int? commandTimeout) where T : class
{
//...
    if (SqlGenerator.SupportsMultipleStatements())
    {
        sql += SqlGenerator.Configuration.Dialect.BatchSeperator + SqlGenerator.IdentitySql(classMap);
        result = connection.Query<long>(sql, entity, transaction, false, commandTimeout, CommandType.Text);
    }
//...
}

2) QueryInternal

while (reader.Read())
{
    yield return (T)func(reader);
}

3) GetIdentitySql

    public override string GetIdentitySql(string tableName)
    {
        return "SELECT LAST_INSERT_ID() AS Id";
    }

Popular Answer

After I check the MySql version, I found that the server version is 5.6, and the connector net is 6.7.4. There is a bug report from mysql website here:

http://bugs.mysql.com/bug.php?id=64084

So I change this method, and it works:

    public override string GetIdentitySql(string tableName)
    {
        return "SELECT CONVERT(LAST_INSERT_ID(), SIGNED INTEGER) AS ID";
    }



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