How to use Parameter List of integers for Oracle with Dapper?

Question

I am trying to re-write some code to use Dapper so I can easily use parameters. I am trying to execute an UPDATE statement on an Oracle database. A list of IDs to UPDATE is passed in as List<int> as parameter. I want to update a field for each of the IDs passed in. The following is what I have:

OracleConnection connection = ... // set earlier

public int IncreaseProcessCount(List<int> ids)
{
    var rowsAffected = connection.Execute(@"UPDATE TABLE SET PROCESSED_COUNT = PROCESSED_COUNT + 1 WHERE ID IN @ids", new { ids });
    return rowsAffected;
}

Before using Dapper, the execution statement was working just fine. Now I am getting following error:

ORA-00936: missing expression.

My current solution is based on below posts:

Dapper query with list of parameters and Performing Inserts and Updates with Dapper

Grice

Try casting your list to an array, as in new { ids.ToArray() } – Verdun

@RobertHarvey no such luck. :( Thanks, though – Grice

Possible duplicate of SELECT * FROM X WHERE id IN (...) with Dapper ORM – Bulbiferous

Your setting of the OracleConnection earlier is a code smell. Likely you're not following the IDisposable pattern correctly. It wouldn't cause the issue you're seeing now, but it's probably something you need to fix. And when working with Oracle, it uses :colons instead of :ampersands to do parameters, as shown here (warning, I contributed the examples for that page). – Bulbiferous

Answer 1

I am not sure if this is Oracle specific issue as I never worked with Oracle + Dapper combination. But I strongly suspect the way you are passing parameter is a problem. The exception "missing expression" is saying the same thing.

Refer modification of your code below:

public int IncreaseProcessCount(int[] ids)
{
    var rowsAffected = connection.Execute(@"UPDATE TABLE SET PROCESSED_COUNT = PROCESSED_COUNT + 1 WHERE ID IN :ids", new { ids });
    return rowsAffected;
}

There are following differences:

1. Use of ":ids" instead of "@ids". I strongly suspect this is an issue because Oracle expects : instead of @ for parameters.
2. Use of int[] instead of List<int>. This should not be an issue because Dapper supports IEnumerable for parameter list; so List should be OK. You have already tried this (as you mentioned in comments) without success.

Refer this question for Dapper with IN clause using Parameter List. Here is another resource.

Edit (for comments):

The core of the problem was use of ":ids" which was correctly included in my answer. I just corrected syntax error in the code above.

Also, I generally use DynamicParameters. Actually, it was not an issue in this case, so I removed that part which was present in first version of my answer. Anyway, following is the code with DynamicParameters which should work equally.

public int IncreaseProcessCount(int[] ids)
{
    var param = new DynamicParameters();
    param.Add(":ids", ids);

    var rowsAffected = connection.Execute(@"UPDATE TABLE SET PROCESSED_COUNT = PROCESSED_COUNT + 1 WHERE ID IN :ids", param);
    return rowsAffected;
}

Insubordinate

@Grice The reason you need to wrap the array in an anonymous object is that you could have other parameters. – Bulbiferous

Answer 2

Based off Amit's answer, below is what I finally got to work. I had to wrap the collection being passed in with an anonymous object.

connection.Execute("UPDATE TABLE SET PROCESSED_COUNT = PROCESSED_COUNT+ 1 WHERE ID IN :ids",
                    new { ids });

Grice