Oracle에 대한 EF 쿼리가 "ORA-12704: 문자 집합 불일치"를 발생시킵니다.

Oracle에 대한 EF 쿼리가 "ORA-12704: 문자 집합 불일치"를 발생시킵니다.

Oracle의 EF에 있는 몇 개의 열을 결합한 다음.Contains()다음과 같은 열 위에:

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

하지만 다음과 같은 예외가 있습니다.

{
  "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)"
  }
}

제가 조회하는 열은 모두 Oracle의 VARCHAR2(128) 유형입니다.

다른 프로젝트에서도 똑같은 코드를 사용하고 있는데 작동합니다.유일한 차이점은 제가 사용하고 있는 프로젝트의 경우Oracle.DataAccess그리고 작동하지 않는 것을 위해, 나는 사용하고 있습니다.Oracle.ManagedDataAccess(사용할 수 없습니다.Oracle.DataAccess(이 프로젝트의 경우).그래서 관리되는 드라이버에 버그/문제가 있는 것 같습니다.

해결책이나 해결책을 알고 있습니다.

저는 결국 이것(ODP)의 저자를 얻었습니다.NetManaged Driver - ORA-12704: 생성된 코드의 문자 집합 불일치) 질문을 업데이트하기 위해 그는 인터셉트를 사용한 해결 방법을 게시했습니다. 여기서 좀 더 자세히 설명하겠습니다.

먼저 구성을 로드하기 위해 DB 컨텍스트를 장식했습니다.다음과 같은 구성이 있는 경우 이를 건너뛰고 구성에 추가할 수 있습니다.

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

구성 클래스를 만듭니다.

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

그런 다음 인터셉터를 만듭니다.

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''", "''");
    }
}

이러한 경우 열이 유니코드인지 여부에 따라 이러한 쿼리의 연결을 수동으로 수행하는 것이 좋습니다.

return _securityUow.Users
    .Where(u => (u.FirstName.ToLower() + DbFunctions.AsNonUnicode(" ") + u.LastName.ToLower() + DbFunctions.AsNonUnicode(" (") + u.NetId.ToLower() + DbFunctions.AsNonUnicode(")")).Contains(search))
    ...

return _securityUow.Users
    .Where(u => (u.FirstName.ToLower() + DbFunctions.AsUnicode(" ") + u.LastName.ToLower() + DbFunctions.AsUnicode(" (") + u.NetId.ToLower() + DbFunctions.AsUnicode(")")).Contains(search))
    ...

NVarchar 또는 Varchar인 경우 열의 유형에 따라 달라집니다(기본적으로 EF는 속성에 .isUnicode가 설정되지 않은 경우 nvarchar를 사용합니다).

IEntity를 구현할 때 HasColumnType에 호출을 추가합니다.TypeConfiguration<>, 나는 다음과 같이 작동합니다.

builder.Property(g => g.PropName).HasColumnName("ColName").HasColumnType("varchar2(40)")

언급URL : https://stackoverflow.com/questions/34645274/ef-query-to-oracle-throwing-ora-12704-character-set-mismatch