An enumerable sequence of parameters (arrays, lists, etc) is not allowed in this context in Dapper

Question

I have the following code:

static void Main(string[] args){
        string sql= "SELECT * FROM Posts WHERE 1=1 ";
        SqlParameter[] @params= SetDynamicParameter(ref sql, "Param=Value", "Param2=Value2", "ParamN=ValueN");

        IDbConnection connection = new SqlConnection(connectionString);
        IEnumerable<Posts> en = connection.Query<Posts>(sql,param:@params);

        Console.ReadKey(true);
}
 static SqlParameter[] SetDynamicParameter(ref string sql,params string[] sarr) {
        string condition = string.Empty;
        List<SqlParameter> list = new List<SqlParameter>();
        foreach (var item in sarr)
        {
            condition += " AND "+item.Split('=')[0] + "=@" + item.Split('=')[0];
            list.Add(new SqlParameter("@" + item.Split('=')[0], item.Split('=')[1]));
        }
        sql += condition;
        return list.ToArray() ;
  }

The output error:An enumerable sequence of parameters (arrays, lists, etc) is not allowed in this context.

How can it be?Is there an equivalent solution?

Answer 1

Try to use Dapper.DynamicParameters instead of SqlParameter array:

var parameters = new DynamicParameters();
parameters.Add("@ParameterName", parameterValue);

Answer 2

I know this is kind of late for you, but maybe it could help somebody else:)

You can do it like this:

public class MySqlDataAccess
{
    string _connectionString = "{your connection string}";

    public async Task<IEnumerable<CustomerDto>> GetListAsync(IList<Guid> customers)
    {
        const string query = @"
            SELECT TOP 100 Id,
                    Name
            FROM Customers
            WHERE Id IN @CustomerIdList
        ";

        using (var c = new SqlConnection(_connectionString))
        {
            return await c.QueryAsync<CustomerDto>(query, new { CustomerIdList = customers.ToArray() });
        }
    }
}

You do a WHERE Id IN @CustomerIdList then passing in an anonymous type from your array like this: new { CustomerIdList = customers.ToArray() } instead of passing the array directly.

Hope it helps!