EF查询到Oracle抛出“ORA-12704：字符集不匹配” [英] EF query to Oracle throwing "ORA-12704: character set mismatch"

问题描述

我正在尝试从Oracle中组合几个列，然后在这样的列上执行 .Contains（）：

  public IEnumerable< User> SearchUsers（string search）
 {
 search = search.ToLower（）; 
 
 return _securityUow.Users 
 .Where（u =>（u.FirstName.ToLower（）++ u.LastName.ToLower（）+（+ u.NetId .ToLower（）+））。包含（搜索）
 .OrderBy（u => u.LastName）
 .ThenBy（u => u.FirstName）
。 AsEnumerable（）; 
} 
  

但是，我收到这个例外：

  {
消息：发生错误，
ExceptionMessage：执行命令时发生错误
ExceptionType：System.Data.Entity.Core.EntityCommandExecutionException，
StackTrace：在SoftwareRegistration.WebUI.Controllers.Api.V1 .UserContactController.Lookup在C（字符串搜索）：\LocalRepository\OnlineSupport\SoftwareRegistration\trunk\release\SoftwareRegistration\SoftwareRegistration.WebUI\Controllers\Api\V1\UserContactController.cs：线40\r\\\
在lambda_method在System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor（封闭，对象，对象[]）\r\\\
<> c__DisplayClass10< GetExecutor> b__9（对象实例，Object [] methodParameters）\r\\\
在System.Web.Http.Controllers .ReflectedHttpActionDescriptor.ActionExecutor.Execute（Object instance，Object [] arguments）\r\\\
在System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync（HttpControllerContext controllerContext，IDictionary`2参数，CancellationToken cancelToken）\r\ n ---从前一个位置引发异常的堆栈跟踪结束---在System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess（任务任务）\r\\\
在System.Runtime.CompilerServices中的\r\\\
 .TaskAwaiter.HandleNonSuccessAndDebuggerNotification（任务任务）\\\\
在System.Runtime.CompilerServices.TaskAwaiter`1.GetResult（）\r\\\
在System.Web.Http.Controllers.ApiControllerActionInvoker。< InvokeActionAsyncCore> d__0 .MoveNext（）\r\\\
 ---从前一个位置抛出异常的堆栈跟踪结束---在System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess（任务任务）\r中的\r\\\
 \\\
在System.Runtime.Compil erServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification（任务任务）\r\\\
在System.Runtime.CompilerServices.TaskAwaiter`1.GetResult（）\r\\\
在System.Web.Http.Controllers.ActionFilterResult。< ExecuteAsync> d__2.MoveNext（）\r\\\
 ---从前一个位置引发异常的堆栈跟踪结束--- \r\\\
在System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess（任务任务）\在System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification（任务任务）上的r\\\
在System.Runtime.CompilerServices.TaskAwaiter`1.GetResult（）\r\\\
在System.Web.Http .Dispatcher.HttpControllerDispatcher。< SendAsync> d__1.MoveNext（），
InnerException：{
Message：发生错误，
ExceptionMessage ORA-12704：字符集不匹配，
ExceptionType：Oracle.ManagedDataAccess.Client.OracleException，
StackTrace：at OracleInternal.Servic eObjects.OracleCommandImpl.VerifyExecution（OracleConnectionImpl connectionImpl，Int32& cursorId，Boolean bThrowArrayBindRelatedErrors，OracleException& exceptionForArrayBindDML，Boolean&在OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader（String commandText，OracleParameterCollection paramColl，CommandType commandType，OracleConnectionImpl connectionImpl，OracleDataReaderImpl& rdrImpl，Int32 longFetchSize，Int64 clientInitialLOBFS，OracleDependencyImpl orclDependencyImpl，Int64 [] scnForExecution，在Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader（Boolean requery，Boolean）中，Int64 []& scnFromExecution，OracleParameterCollection& bindByPositionParamColl，Boolean& bBindParamPresent，Int64& internalInitialLOBFS，OracleException& exceptionForArrayBindDML，Boolean isDescribeOnly，Boolean isFromEF）\r\\\
 fillRequest，的CommandBehavior行为）\r\\\
在Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader（的CommandBehavior行为）\r\\\
在System.Data.Common.DbCommand.ExecuteReader（的CommandBehavior行为）\r\在系统em.Data.Entity.Infrastructure.Interception.DbCommandDispatcher<读者与GT; b__c（的DbCommand吨，DbCommandInterceptionContext`1 c）中\r\\\
在System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch [t目标，TInterceptionContext，TResult]（TTarget target，Func`3操作，TInterceptionContext interceptionContext，Action`3执行，Action`3执行）\r\\\
在System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader（DbCommand命令，DbCommandInterceptionContext interceptionContext）\r\\\
在System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader（CommandBehavior行为）\r\\\
在System.Data.Common.DbCommand.ExecuteReader（CommandBehavior行为）\r\\ \\ n在System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands（EntityCommand entityCommand，CommandBehavior行为）
} 
} 
  

我正在查询的列是全部的pe VARCHAR2（128）在Oracle中。

我还在另一个项目中使用完全相同的代码，它的工作原理。唯一的区别是，对于我正在使用 Oracle.DataAccess 的项目，对于那些不起作用的项目，我使用 Oracle.ManagedDataAccess （我无法为此项目使用 Oracle.DataAccess ）。所以我相信管理的驱动程序有一个错误/问题。

我可以解决方案或解决方法。

解决方案

我最终得到这个作者（ ODP.Net托管驱动程序 - ORA-12704：生成的代码中的字符集不匹配）为了更新问题，他发布了一个使用拦截器的解决方法，我会在这里更多的细节...

首先，我装饰了我的DBContext加载配置。你可以跳过这个，只要你有一个配置：

  [DbConfigurationType（typeof（MyDbConfiguration））] 
 public partial class MyContext：DbContext 
  

创建配置类：

  public class MyDbConfiguration：DbConfiguration 
 {
 public MyDbConfiguration（）
 {
 this.AddInterceptor（new NVarcharInterceptor ））; //将此行添加到现有配置。 
} 
} 
  

接下来，创建拦截器：

  public class NVarcharInterceptor：IDbCommandInterceptor 
 {
 public void NonQueryExecuted（DbCommand command，DbCommandInterceptionContext< int> interceptionContext）
 $ {
 if（command！= null& ; 
} 
 
 public void NonQueryExecuting（DbCommand命令，DbCommandInterceptionContext< int> interceptionContext）
 {
 if（command！= null&！string.IsNullOrWhiteSpace command.CommandText））
 command.CommandText = command.CommandText.Replace（N，）; 
} 
 
公共无效ReaderExecuted（的DbCommand命令，DbCommandInterceptionContext< DbDataReader> interceptionContext）
 {
如果（命令= NULL&安培;！&安培;！string.IsNullOrWhiteSpace（ command.CommandText））
 command.CommandText = command.CommandText.Replace（N，）; 
} 
 
公共无效ReaderExecuting（的DbCommand命令，DbCommandInterceptionContext< DbDataReader> interceptionContext）
 {
如果（命令= NULL&安培;！&安培;！string.IsNullOrWhiteSpace（ command.CommandText））
 command.CommandText = command.CommandText.Replace（N，）; 
} 
 
 public void ScalarExecuted（DbCommand命令，DbCommandInterceptionContext< object> interceptionContext）
 {
 if（command！= null&！string.IsNullOrWhiteSpace command.CommandText））
 command.CommandText = command.CommandText.Replace（N，）; 
} 
 
 public void ScalarExecuting（DbCommand命令，DbCommandInterceptionContext< object> interceptionContext）
 {
 if（command！= null&！string.IsNullOrWhiteSpace command.CommandText））
 command.CommandText = command.CommandText.Replace（N，）; 
} 
} 
  

I'm trying to combine a few columns in EF from Oracle then do a .Contains() over the columns like this:

public IEnumerable<User> SearchUsers(string search)
{
    search = search.ToLower();

    return _securityUow.Users
            .Where(u => (u.FirstName.ToLower() + " " + u.LastName.ToLower() + " (" + u.NetId.ToLower() + ")").Contains(search))
            .OrderBy(u => u.LastName)
            .ThenBy(u => u.FirstName)
            .AsEnumerable();
}

However, I'm getting this exception:

{
  "Message": "An error has occurred.",
  "ExceptionMessage": "An error occurred while executing the command definition. See the inner exception for details.",
  "ExceptionType": "System.Data.Entity.Core.EntityCommandExecutionException",
  "StackTrace": "   at SoftwareRegistration.WebUI.Controllers.Api.V1.UserContactController.Lookup(String search) in C:\LocalRepository\OnlineSupport\SoftwareRegistration\trunk\release\SoftwareRegistration\SoftwareRegistration.WebUI\Controllers\Api\V1\UserContactController.cs:line 40\r\n   at lambda_method(Closure , Object , Object[] )\r\n   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)\r\n   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)\r\n   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()",
  "InnerException": {
    "Message": "An error has occurred.",
    "ExceptionMessage": "ORA-12704: character set mismatch",
    "ExceptionType": "Oracle.ManagedDataAccess.Client.OracleException",
    "StackTrace": "   at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)\r\n   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, Boolean isDescribeOnly, Boolean isFromEF)\r\n   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)\r\n   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)\r\n   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)\r\n   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)\r\n   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)"
  }
}

The columns I'm querying are all of type VARCHAR2(128) in Oracle.

I'm also using this exact same code in another project and it works. The only difference is that for the project that works I'm using Oracle.DataAccess and for the one that doesn't work, I'm using Oracle.ManagedDataAccess (I am unable to use Oracle.DataAccess for this project). So I believe there is a bug/problem in the managed driver.

I'm open to solutions or workarounds.

解决方案

I ended up getting the author of this (ODP.Net Managed Driver - ORA-12704: character set mismatch in generated code) to update the question, he posted a workaround using an interceptor, I'll go a bit more detail here...

First, I decorated my DBContext to load a configuration. you can skip this and just add to your configuration if you have one:

[DbConfigurationType(typeof(MyDbConfiguration))]
public partial class MyContext : DbContext

Create the config class:

public class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
        this.AddInterceptor(new NVarcharInterceptor()); //add this line to existing config.
    }
}

Next, create the interceptor:

public class NVarcharInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        if (command != null && !string.IsNullOrWhiteSpace(command.CommandText))
            command.CommandText = command.CommandText.Replace("N''", "''");
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        if (command != null && !string.IsNullOrWhiteSpace(command.CommandText))
            command.CommandText = command.CommandText.Replace("N''", "''");
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        if (command != null && !string.IsNullOrWhiteSpace(command.CommandText))
            command.CommandText = command.CommandText.Replace("N''", "''");
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        if (command != null && !string.IsNullOrWhiteSpace(command.CommandText))
            command.CommandText = command.CommandText.Replace("N''", "''");
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        if (command != null && !string.IsNullOrWhiteSpace(command.CommandText))
            command.CommandText = command.CommandText.Replace("N''", "''");
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        if (command != null && !string.IsNullOrWhiteSpace(command.CommandText))
            command.CommandText = command.CommandText.Replace("N''", "''");
    }
}

这篇关于EF查询到Oracle抛出“ORA-12704：字符集不匹配”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！