Under EF6, ODP.NET incorrectly handles null checks for varchar2 columns resulting in “ORA-12704: character set mismatch” errors.
Nuget Package List:
Oracle Database Version Info:
SELECT BANNER_FULL FROM v$version
BANNER_FULL
-----------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
Database Charset Configuration
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE "PARAMETER" LIKE '%CHAR%'
PARAMETER VALUE
--------------------------------------- -------------------------------------------------------------
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
Target Framework
.NET Framework 4.8
Example App.config
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
<section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.122.21.1, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</configSections>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
</startup>
<entityFramework>
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.122.21.1, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<remove invariant="Oracle.ManagedDataAccess.Client" />
<add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver" type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.122.21.1, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories>
</system.data>
<connectionStrings>
<add name="TestEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=Oracle.ManagedDataAccess.Client;provider connection string="DATA SOURCE=TestServer.example.com:1521/TestServer;PASSWORD=********;PERSIST SECURITY INFO=True;USER ID=TestUser"" providerName="System.Data.EntityClient" />
</connectionStrings>
</configuration>
Example Edmx Mapping
<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
<!-- EF Runtime content -->
<edmx:Runtime>
<!-- SSDL content -->
<edmx:StorageModels>
<Schema Namespace="Model.Store" Provider="Oracle.ManagedDataAccess.Client" ProviderManifestToken="18.0" Alias="Self" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
<EntityType Name="LOOKUP_LIST">
<Key>
<PropertyRef Name="LISTID" />
</Key>
<Property Name="LISTID" Type="number" Precision="10" Scale="0" Nullable="false" />
<Property Name="NAME" Type="varchar2" MaxLength="1000" />
<Property Name="DESCRIPTION" Type="varchar2" MaxLength="2000" />
</EntityType>
<EntityContainer Name="ModelStoreContainer">
<EntitySet Name="LOOKUP_LIST" EntityType="Self.LOOKUP_LIST" Schema="TEST" store:Type="Tables" />
</EntityContainer>
</Schema>
</edmx:StorageModels>
<!-- CSDL content -->
<edmx:ConceptualModels>
<Schema Namespace="Model" Alias="Self" annotation:UseStrongSpatialTypes="false" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm">
<EntityType Name="LOOKUP_LIST">
<Key>
<PropertyRef Name="LISTID" />
</Key>
<Property Name="LISTID" Type="Int32" Nullable="false" />
<Property Name="NAME" Type="String" MaxLength="1000" FixedLength="false" Unicode="false" />
<Property Name="DESCRIPTION" Type="String" MaxLength="2000" FixedLength="false" Unicode="false" />
</EntityType>
<EntityContainer Name="TestEntities" annotation:LazyLoadingEnabled="true">
<EntitySet Name="LOOKUP_LIST" EntityType="Self.LOOKUP_LIST" />
</EntityContainer>
</Schema>
</edmx:ConceptualModels>
<!-- C-S mapping content -->
<edmx:Mappings>
<Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
<EntityContainerMapping StorageEntityContainer="ModelStoreContainer" CdmEntityContainer="TestEntities">
<EntitySetMapping Name="LOOKUP_LIST">
<EntityTypeMapping TypeName="Model.LOOKUP_LIST">
<MappingFragment StoreEntitySet="LOOKUP_LIST">
<ScalarProperty Name="NAME" ColumnName="NAME" />
<ScalarProperty Name="DESCRIPTION" ColumnName="DESCRIPTION" />
<ScalarProperty Name="LISTID" ColumnName="LISTID" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
</EntityContainerMapping>
</Mapping>
</edmx:Mappings>
</edmx:Runtime>
<!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
<Designer xmlns="http://schemas.microsoft.com/ado/2009/11/edmx">
<Connection>
<DesignerInfoPropertySet>
<DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
</DesignerInfoPropertySet>
</Connection>
<Options>
<DesignerInfoPropertySet>
<DesignerProperty Name="ValidateOnBuild" Value="true" />
<DesignerProperty Name="EnablePluralization" Value="false" />
<DesignerProperty Name="IncludeForeignKeysInModel" Value="false" />
<DesignerProperty Name="UseLegacyProvider" Value="false" />
<DesignerProperty Name="CodeGenerationStrategy" Value="None" />
</DesignerInfoPropertySet>
</Options>
<!-- Diagram content (shape and connector positions) -->
<Diagrams></Diagrams>
</Designer>
</edmx:Edmx>
Example Program
using System;
using System.Linq;
using System.Text;
namespace ConsoleApp1
{
internal class Program
{
static void Main(string[] args)
{
using (var context = new TestEntities())
{
var logText = new StringBuilder();
context.Database.Log = x => logText.Append(x);
try
{
var listText = context.LOOKUP_LIST
.Select(x => new
{
x.LISTID,
FullText = x.NAME + " - " + x.DESCRIPTION
})
.ToList();
}
catch(Exception)
{
Console.WriteLine(logText.ToString());
throw;
}
}
}
}
}
Example Output
Opened connection at 8/3/2023 6:43:44 PM -06:00
SELECT
"Extent1"."LISTID" AS "LISTID",
((((CASE WHEN ("Extent1"."NAME" IS NULL) THEN N'' ELSE "Extent1"."NAME" END)||(' - ')))||(CASE WHEN ("Extent1"."DESCRIPTION" IS NULL) THEN N'' ELSE "Extent1"."DESCRIPTION" END)) AS "C1"
FROM "TEST"."LOOKUP_LIST" "Extent1"
-- Executing at 8/3/2023 6:43:45 PM -06:00
-- Failed in 37 ms with error: ORA-12704: character set mismatch
Closed connection at 8/3/2023 6:43:45 PM -06:00
Unhandled Exception: System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> Oracle.ManagedDataAccess.Client.OracleException: ORA-12704: character set mismatch
at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
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, Int64 internalInitialJSONFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<>c.<Reader>b__6_0(DbCommand t, DbCommandInterceptionContext`1 c)
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)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
--- End of inner exception stack trace ---
at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass41_0.<GetResults>b__1()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass41_0.<GetResults>b__0()
at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__31_0()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at ConsoleApp1.Program.Main(String[] args) in C:\NGWebApps\ACePTS\ConsoleApp1\Program.cs:line 31
Workarounds:
The only workaround I've found is to move the string concatenation out of the Linq IQueryable expression into client side evaluation.
There is also someone who found a workaround with a custom IDbCommandInterceptor, (https://stackoverflow.com/a/34665239) but as pointed out in the comments, that workaround can introduce its own set of problems. And it seems like a heavy-handed way to workaround this problem to begin with.
Wrapping with DbFunctions.AsUnicode(...) or DbFunctions.AsNonUnicode(...) is ineffective because any such wrapping will occur outside of the problematic area in the generated CASE statement.