Oracle ManagedDataAccess.EntityFramework Database.SqlQuery通过位置绑定参数? [英] Oracle ManagedDataAccess.EntityFramework Database.SqlQuery binding parameters by position?

问题描述

我有以下代码:

        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;

这将产生"ORA-01008:并非所有变量都已绑定".我怀疑变量绑定的方式出了问题，最终尝试这样做:

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:

"ODP.NET和Entity Framework支持绑定标量参数.在Entity Framework中，支持按名称绑定参数.不支持按位置绑定."

"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."

我以某种方式认为文档对我撒谎，并且试图按位置绑定.我记得很久以前在EF支持之前已解决此问题，但我不记得该修复了什么，更不用说如何在EF中应用相同的技术了.

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?

推荐答案

问题是Database.SqlQuery方法使用基础DbConnection的CreateCommand方法.在ODP.NET中，这导致一个OracleCommand，该默认情况下按位置(BindByName = false)绑定参数.

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 ).

这种行为是不可配置的，没有改变它的好地方.作为一种解决方法，我建议使用自定义的SqlQuery方法替换，该替换将使用BindByName = true创建OracleCommand，执行ExecuteReader并使用ObjectContext.Translate方法进行映射:

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

要使用它，只需替换

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

致电

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

这篇关于Oracle ManagedDataAccess.EntityFramework Database.SqlQuery通过位置绑定参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！