Collection parameters querying oracle with dapper

Question

Having issue while passing the array of parameters in the WEB API service

public class SampleController : ApiController
{
   public string Getdetails([FromUri] int[] id) 
   {
    var inconditions = id.Distinct().ToArray();
    using (var dbConn = new OracleConnection("DATA SOURCE=h;PASSWORD=C;PERSIST SECURITY INFO=True;USER ID=T"))
    {
        dbConn.Open();
        var strQuery = "SELECT PRIO_CATEGORY_ID AS PRIO, LANG_ID AS LANG, REC_DATE AS REC, REC_USER AS RECUSER, DESCR, COL_DESCR AS COL, ROW_DESCR AS DROW, ABBR FROM STCD_PRIO_CATEGORY_DESCR WHERE REC_USER  IN (:p)";
        var queryResult = dbConn.Query<SamModel>(strQuery, new { p = inconditions });
        return JsonConvert.SerializeObject(queryResult);
    }
}

Now while calling the API as http://localhost:35432/api/Sample?id=1&id=83 it throws an error saying on var queryResult = dbConn.Query(strQuery);

{"ORA-00907: missing right parenthesis"}

Checked all the parenthesis and everything looks good

Answer 1

There is no need to wrap your collection parameters in parenthesis. Dapper is not just find-and-replace, and smart enought to handle collection parameters it by itself. Try this:

... WHERE REC_USER IN :p

Check out List Support section.

In your (:p) case following kind of query generated:

... WHERE REC_USER IN ((1,2,3))

Answer 2

@lorond got it right with his comment and answer.

The links he provided confirm that your code can be refactored to

public class SampleController : ApiController {
   public string Getdetails([FromUri] int[] id) {
    var inconditions = id.Distinct().ToArray();
    using (var dbConn = new OracleConnection("DATA SOURCE=h;PASSWORD=C;PERSIST SECURITY INFO=True;USER ID=T")) {
        dbConn.Open();
        var strQuery = "SELECT PRIO_CATEGORY_ID AS PRIO, LANG_ID AS LANG, REC_DATE AS REC, REC_USER AS RECUSER, DESCR, COL_DESCR AS COL, ROW_DESCR AS DROW, ABBR FROM STCD_PRIO_CATEGORY_DESCR WHERE REC_USER IN @p";
        var queryResult = dbConn.Query<SamModel>(strQuery, new { p = inconditions });
        return JsonConvert.SerializeObject(queryResult);
    }
}