Geek Answers Handbook

EF query for Oracle casting "ORA-12704: Character Set Mismatch"

I am trying to combine multiple columns in EF from Oracle and then do .Contains() column by column 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 get 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)" } } OracleConnectionImpl connectionImpl, Int32 & cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException & exceptionForArrayBindDML, Boolean & hasMoreRowsInDB, Boolean bFirstIterationDone) \ r \ n at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader (String commandText, OracleParameterCollection paramColl { "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)" } } & rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64 [] scnForExecution, Int64 [] & scnFromExecution, OracleParameterCollection & bindByPositionParamColl, Boolean & bBindParamPresent, Int64 & internalInitialLOBFS, OracleException & exceptionForArrayBindDML, Boolean isDescribeOnly, Boolean isFromEF) \ r { "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 also use this exact code in another project and it works. The only difference is that for the project that works, I use Oracle.DataAccess , and for the one that does not work, I use Oracle.ManagedDataAccess (I cannot use Oracle.DataAccess for this project). Therefore, I believe that there is an error / problem in the managed driver.

I am open to solutions or workarounds.

+7
c # oracle entity-framework
source share
1 answer

As a result, I got the author of this ( ODP.Net managed driver - ORA-12704: character set mismatch in the generated code ) to update the question, he published a workaround using an interceptor, I will tell you more in more detail ...

First, I decorated my DBContext to load the 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. } }

Then create an 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''", "''"); } }
+14
source share

More articles:


All Articles