Using custom Dapper ITypeHandler for parsing data but cause conflict to handle SQL parameters

[cycbluesky]

I use custom Dapper ITypeHandler to automap between C# string[] and mysql Json field. But it causes confict to handle SQL parameters

1. table

CREATE TABLE if NOT EXISTS post ( id bigint NOT NULL AUTO_INCREMENT, approver json NULL COMMENT 'approvers, null or array' PRIMARY KEY (id) }

2. mapping class public class Post { public long Id { get; set; } public string[] Approver { get; set; } }

3. custom Dapper ITypeHandler ` public class JsonTypeHandler<T> : SqlMapper.TypeHandler<T> { public override T? Parse(object value) { if (value is string json) { return JsonSerializer.Deserialize<T>(json, Consts.Genernal.JsonSerializerOptions); }

        return default;
    }
   
    public override void SetValue(IDbDataParameter parameter, T? value)
    {
        parameter.DbType = DbType.String;
        parameter.Value = JsonSerializer.Serialize(value, Consts.Genernal.JsonSerializerOptions);
    }
   

   }`

4. register custom typehandler SqlMapper.AddTypeHandler(new JsonTypeHandler<int[]>());

5. confilct when using IN clause (from hangfire.mysql)

var connectionString = "your mysql connectionstring"; var queues = new string[] { "default"}; using var connection = new MySqlConnection(connectionString); int nUpdated = connection.Execute( $"updateJobQueue set FetchedAt = UTC_TIMESTAMP(), FetchToken = @fetchToken " + "where (FetchedAt is null or FetchedAt < DATE_ADD(UTC_TIMESTAMP(), INTERVAL @timeout SECOND)) " + " and Queue in @queues " + "LIMIT 1;", new { queues = queues, timeout = 60, fetchToken = Guid.NewGuid().ToString() });

the above code throws exception: MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''["default"]' LIMIT 1' at line 1