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

Try casting your list to an array, as in `new { ids.ToArray() }` — Robert Harvey, Feb 01 '18 at 21:27
Possible duplicate of [SELECT \* FROM X WHERE id IN (...) with Dapper ORM](https://stackoverflow.com/questions/8388093/select-from-x-where-id-in-with-dapper-orm) — mason, Feb 02 '18 at 13:25
Your setting of the OracleConnection earlier is a code smell. Likely you're not following the [IDisposable](https://msdn.microsoft.com/en-us/library/system.idisposable(v=vs.110).aspx) 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](http://bobby-tables.com/csharp) (warning, I contributed the examples for that page). — mason, Feb 02 '18 at 13:27

score 8 · Answer 1 · edited Jun 20 '20 at 09:12

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:

Use of ":ids" instead of "@ids". I strongly suspect this is an issue because Oracle expects : instead of @ for parameters.
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;
}

@jollyroger23 The reason you need to wrap the array in an anonymous object is that you *could* have other parameters. — mason, Feb 02 '18 at 14:06

score 1 · Answer 2 · edited Feb 02 '18 at 14:16

1

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 });

edited Feb 02 '18 at 14:16

mason

31,774
10
77
121

answered Feb 02 '18 at 14:11

jollyroger23

665
1
6
19

score 0 · Answer 3 · edited Feb 02 '18 at 13:23

I don't know Dapper (never even heard of it) so I apologize if this is a nonsense; however, in this:

WHERE ID IN @ids

IN suggests that Oracle expects a list of elements enclosed into parentheses. So, what is @ids really? If it is a single value, parentheses aren't necessary. But, if there are two or more of them (though, up to 1000), they have to be separated by a comma and - as I said - enclosed into parentheses, such as (1, 2, 8). I suppose you've already separated them - now try to add () and see what happens.

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

3 Answers3

Edit (for comments):