ODP.Net Managed Driver - ORA-12704: Character Set Mismatch in Generated Code

Question

I am currently using Oracle Managed Driver (v12.1.2400) as my Entity Framework driver and currently see ORA-12704: character set mismatch error at runtime.

The LINQ-> SQL code that I'm using looks like this:

 from c in CUSTOMER.AsNoTracking() where c.ACCOUNT.Contains("DE") && c.DELETED == "N" orderby (c.FORENAME + c.SURNAME) select new { c.ACCOUNT, c.FORENAME, c.SURNAME}) 

and this creates the following SQL:

 SELECT "Project1"."C2" AS "C1", "Project1"."ACCOUNT" AS "ACCOUNT", "Project1"."FORENAME" AS "FORENAME", "Project1"."SURNAME" AS "SURNAME" FROM ( SELECT( (CASE WHEN ("Extent1"."FORENAME" IS NULL) THEN N'' ELSE "Extent1"."FORENAME" END) ||(CASE WHEN ("Extent1"."SURNAME" IS NULL) THEN N'' ELSE "Extent1"."SURNAME" END)) AS "C1", "Extent1"."ACCOUNT" AS "ACCOUNT", "Extent1"."FORENAME" AS "FORENAME", "Extent1"."SURNAME" AS "SURNAME", 1 AS "C2" FROM "TEST"."CUSTOMER" "Extent1" WHERE (("Extent1"."ACCOUNT" LIKE '%DE%') AND ('N' = "Extent1"."DELETED"))) "Project1" ORDER BY "Project1"."C1" ASC; 

When I debug this SQL, I see that the problem is that SQL uses N'' in CASE sections. Since these columns are not Unicode, if I delete the previous N to leave only '' , then sql works as expected.

Can this be prevented by default?

All db columns are currently VARCHAR and modeled in C # as string .
The first code mappings for the two columns are as follows:

 this.Property(t => t.FORENAME).HasColumnName("FORENAME").IsUnicode(false).HasMaxLength(35); this.Property(t => t.SURNAME).HasColumnName("SURNAME").IsUnicode(false).HasMaxLength(35); 

I expected the IsUnicode(false) statement to take care of this.

FYI, this worked when I used EF5 and an unmanaged driver.
Also, the Devart dotConnectForOracle drivers do not have this problem, so I think this is a bug in the Oracle drivers.

Answer 1

I never found the right solution, but I found a workaround that works well.

I created the Interceptor class NVarcharInterceptor that implements IDbCommandInterceptor , and overriding all ..Executing(..) methods to contain the following code:

 if (command != null && !string.IsNullOrWhiteSpace(command.CommandText)) command.CommandText = command.CommandText.Replace("N''", "''"); 

This effectively removes any unwanted NVarchar links from any command executed on my DbContext .

To add an interceptor, I added the following code: DBConfiguration class:

 this.AddInterceptor(new NVarcharInterceptor()); 

Answer 2

You can also create a custom function to concatenate strings and call it in LINQ instead of directly concatenating strings.

For example: Create a database function.

 create or replace FUNCTION CONCAT2 ( PARAM1 IN VARCHAR2 , PARAM2 IN VARCHAR2 ) RETURN VARCHAR2 AS BEGIN RETURN PARAM1 || PARAM2; END CONCAT2; 

Add it to your model. I use the first approach to the database, so I just had to update the model. Card function in code:

 using System; using System.Data.Entity; public static class DbFunctions { [DbFunction("Model.Store", "CONCAT2")] public static string Concat(string arg1, string arg2) { return String.Concat(arg1, arg2); } } 

And use it in your LINQ queries:

 from c in CUSTOMER.AsNoTracking() where c.ACCOUNT.Contains("DE") && c.DELETED == "N" orderby DbFunctions.Concat(c.FORENAME, c.SURNAME) select new { c.ACCOUNT, c.FORENAME, c.SURNAME})