问题描述
我有以下代码:
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通过位置绑定参数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!