Dapper, Oracle and Command Parameter

c# dapper oracle orm parameters

Question

I have a table in Oracle named MESSAGE and it has a column TERMINALID (VARCHAR2 8 NOT NULL); and there is an index for this column. There are about 300,000,000 records in this table.

Now, this works fine (0.0 seconds):

using (var con = new OracleConnection(connectionString2))
{
    try
    {
        con.Open();

        var parameters = new DynamicParameters();
        parameters.Add("PTerminalId", value: "04447777", dbType: System.Data.DbType.AnsiString);
        Console.WriteLine("ora - messages #{0}", con.Query<decimal>("SELECT COUNT(*) FROM MESSAGE WHERE TERMINALID=:PTerminalId", param: parameters).FirstOrDefault());
    }
    finally { con.Close(); }
}

And this fails (times out):

using (var con = new OracleConnection(Properties.Settings.Default.GSMConnectionString2))
{
    try
    {
        con.Open();

        Console.WriteLine("ora - messages #{0}", con.Query<decimal>("SELECT COUNT(*) FROM GSM.MESSAGE WHERE TERMINALID=:PTerminalId", param: new { PTerminalId = "04447777" }).FirstOrDefault());
    }
    finally { con.Close(); }
}

Why it's so?

Accepted Answer

Basically: what @Vincent said: a datatype mismatch can make indexes unusable.

Without additional information, dapper makes some assumptions. One of those assumptions is that your string is going to continue to be unicode at the database, so it defaults to DbType.String. In most cases this is either correct or adequate, but it can misbehave in some scenarios - which is why we also provide the facility (via DynamicParameters) to be more explicit about both the type and length of the string parameter to add.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why