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