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?

That's because Dapper wants something with a set of properties and values, not a list of sql parameters. I think you can create a `Dictionary` instead where the key is the parameter name (without the @) and the value is the value you want to set to the parameter. The idea is that Dapper does the property creation for you. — juharr, Apr 25 '17 at 14:23
Probably you will find something in this stackoverflow thread. [http://stackoverflow.com/questions/33681176/dapper-multi-insert-returning-inserted-objects] — , Apr 25 '17 at 14:24

score 23 · Answer 1 · edited May 05 '19 at 08:38

23

Try to use Dapper.DynamicParameters instead of SqlParameter array:

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

edited May 05 '19 at 08:38

Vahid Farahmandian

6,081
7
42
62

answered Nov 14 '17 at 17:37

scode102

329
4
6

score 5 · Answer 2 · answered Apr 14 '20 at 11:19

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!

You can shorten it some by doing `WHERE Id IN @customers` and `await c.QueryAsync(query, new { customers });`, it should works just as fine. — emilsteen, Jan 19 '21 at 20:57

score 1 · Answer 3 · edited May 05 '19 at 08:40

If you use ExecuteNonQuery instead of Query<>, you will no longer get this exception. But another problem will arise as you use Query in Oracle.

My solution to this problem was to use a intermediary table. I was using Oracle; Oracle has a special table called "dual" which is under sys schema. This table gave me inspiration and I created another table under my user's schema.

CREATE TABLE USER_SCHEMA.DUAL2
(
  DUMMY  VARCHAR2(1000 BYTE)
)
NOCOMPRESS 
NOCACHE
NOPARALLEL;

You can extend length if you wish. Gave some grants to table. Then I changed my query from select to insertion for select statement.

INSERT INTO USER_SCHEMA.DUAL2
   (SELECT ID
      FROM ANOTHER_SCHEMA.SOME_TABLE
     WHERE SOME_FLAG = 'M' AND NO IN :NO)

This is because ADO.NET is not able to return a result from ExecuteNonQuery. By default it runs SELECT queries but returns -1 for each query. I am simulating an INSERT by providing a SELECT query. This returns a result.

var parameters = noSplit.Select(x => new { NO = x, SOME_FLAG = flag }).ToList();

var queryResult = dbConnection.Execute(insertSelectQuery, parameters, transactionIfExists);

if (queryResult != parameters.Count)
{
    throw new Exception("Can not find some no");
}

score 1 · Answer 4 · answered Feb 19 '22 at 13:19

Dapper will take an IEnumerable as the param argument and insert multiple records. This is what I was passing, so I was confused when I got the error message above.

My mistake was that I copied and pasted from another method that was calling connection.Query instead of connection.Execute.

So, if your query is already specifying the columns, also check that you're calling Execute and not some other method.

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

4 Answers4