Oracle ManagedDataAccess.EntityFramework Database.SqlQuery binding parameters by position?

Question

I have the following code:

        var query = Database.SqlQuery<int>(@"

            SELECT CASE WHEN EXISTS (
                SELECT 1 
                FROM v$session v, UsersXxxx u
                WHERE v.Client_Info LIKE u.UserName || ';%' 
                AND v.UserName = :schemaName
                AND u.SchemaName = :schemaName
                AND v.module = 'XXXX.exe' 
                AND u.UserKey = :userKey)
            THEN 1 ELSE 0 END AS LoggedIn FROM DUAL",

            new OracleParameter("schemaName", schemaName),
            new OracleParameter("userKey", userKey));

        return query.First() != 0;

Which produces a "ORA-01008: not all variables bound". I suspected something was up with the way the variables are being bound and ended up trying this:

        var query = Database.SqlQuery<int>(@"

            SELECT CASE WHEN EXISTS (
                SELECT 1 
                FROM v$session v, UsersXxxx u
                WHERE v.Client_Info LIKE u.UserName || ';%' 
                AND v.UserName = :schemaName
                AND u.SchemaName = :schemaName
                AND v.module = 'XXXX.exe' 
                AND u.UserKey = :userKey)
            THEN 1 ELSE 0 END AS LoggedIn FROM DUAL",

            new OracleParameter("asdf", schemaName),
            new OracleParameter("fdsa", schemaName),
            new OracleParameter("userKey", userKey));

        return query.First() != 0;

Which works like a charm! I poked around docs and found a blurb that says:

"Binding scalar parameters is supported with ODP.NET and Entity Framework. In Entity Framework, parameter binding by name is supported. Binding by position is not supported."

Somehow I think the docs are lying to me and it's trying to bind by position. I remember fixing this once long ago before the EF support, but I cannot remember what the fix was, much less how to apply the same technique in EF.

My workaround, although kludgy, works but isn't there an option somewhere to make it bind by name instead of by position? If so, what is it?

Answer 1

The problem is that Database.SqlQuery methods uses the CreateCommand method of the underlying DbConnection. In ODP.NET this leads to a OracleCommand which by default binds parameters by position ( BindByName = false ).

That behavior is not configurable and there is no good place for changing it. As a workaround, I could suggest using a custom SqlQuery method replacement, which would create the OracleCommand with BindByName = true, do ExecuteReader and use the ObjectContext.Translate method to do the mapping:

public static class EFExtensions
{
    public static IEnumerable<T> DbQuery<T>(this DbContext db, string sql, params object[] parameters)
    {
        if (parameters != null && parameters.Length > 0 && parameters.All(p => p is OracleParameter))
            return OracleDbQuery<T>(db, sql, parameters);
        return db.Database.SqlQuery<T>(sql, parameters);
    }

    private static IEnumerable<T> OracleDbQuery<T>(DbContext db, string sql, params object[] parameters)
    {
        var connection = db.Database.Connection;
        var command = connection.CreateCommand();
        ((OracleCommand)command).BindByName = true;
        command.CommandText = sql;
        command.Parameters.AddRange(parameters);
        connection.Open();
        try
        {
            using (var reader = command.ExecuteReader())
            using (var result = ((IObjectContextAdapter)db).ObjectContext.Translate<T>(reader))
            {
                foreach (var item in result)
                    yield return item;
            }
        }
        finally
        {
            connection.Close();
            command.Parameters.Clear();
        }
    }
}

In order to use it, simply replace

context.Database.SqlQuery<..>(...)

call with

context.DbQuery<..>(...)

Answer 2

In in a recent version of the managed driver you are able to add a web.config entry that sets BindByName to true by default.

<oracle.manageddataaccess.client>
<version number="*">
  <settings>
    <setting name="BindByName" value="true" />
  </settings>
</version>
</oracle.manageddataaccess.client>