.net - MySQL binding multiple parameters to a single query

Question

I have a MySql database, and I'm connecting to it from a .Net app using Dapper. I have the following code:

await connection.ExecuteAsync(
    "DELETE FROM my_data_table WHERE somedata IN (@data)", 
    new { data = datalist.Select(a => a.dataitem1).ToArray() },
    trans);

When I do this with more than a single value, I get the following error:

MySqlConnector.MySqlException: 'Operand should contain 1 column(s)'

Is what I'm trying to do possible in MySql / Dapper, or do I have to issue a query per line I wish to delete?

question from:https://stackoverflow.com/questions/65936367/mysql-binding-multiple-parameters-to-a-single-query

Answer 1

Your original code was almost fine. You just need to remove the parentheses around the parameter. Dapper will insert those for you:

await connection.ExecuteAsync(
    "DELETE FROM my_data_table WHERE somedata IN @data", 
    new { data = datalist.Select(a => a.dataitem1).ToArray() },
    trans);