ODP.Net 托管驱动程序 - ORA-12704:生成的代码中的字符集不匹配 [英] ODP.Net Managed Driver - ORA-12704: character set mismatch in generated code

问题描述

我目前使用 Oracle 托管驱动程序 (v12.1.2400) 作为我的实体框架驱动程序，并且目前在执行期间看到 ORA-12704: character set mismatch 错误.

I am currently using the Oracle Managed Driver (v12.1.2400) as my Entity Framework driver, and am currently seeing a ORA-12704: character set mismatch error during execution.

我使用的LINQ->SQL代码如下:

The LINQ->SQL code I am using is as follows:

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

这将创建以下 SQL:

and this is creating 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;

当我调试那个 SQL 时，我可以看到问题是 SQL 在 CASE 部分中使用了 N''.由于列不是 unicode，如果我删除前面的 N 只留下 '' 则 sql 按预期工作.

When I debug that SQL, I can see the issue is that the SQL is using N'' in the CASE sections. AS teh columns are not unicode, if I remove the preceding N to leave just '' then the sql works as expected.

有什么办法可以防止这种默认情况发生吗?

Is there any way I can prevent this defaulting?

所有 db 列当前都是 VARCHAR，并在 C# 中建模为 string.
两列的代码优先映射如下:

All db columns are currently VARCHAR, and are modeled in C# as string.
Code first 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);

我期待 IsUnicode(false) 语句会处理这个问题.

I was expecting that the IsUnicode(false) statement would take care of this.

仅供参考，当我使用 EF5 和非托管驱动程序时，这曾经可以工作.
另外，Devart dotConnectForOracle 驱动没有这个问题，所以我认为这是 Oracle 驱动中的一个错误.

FYI, this used to work when I used EF5 and the non-managed driver.
In addition, the Devart dotConnectForOracle drivers dont have this issue, so I am thinking this is a bug in the Oracle drivers.

推荐答案

我从来没有找到合适的解决方案，但是我确实找到了一个行之有效的解决方法.

I never did find the proper solution to this, however I did find a workaround that works well.

我创建了一个实现 IDbCommandInterceptor 的拦截器类 NVarcharInterceptor，并覆盖了所有 ..Executing(..) 方法以包含以下内容代码:

I created an Interceptor class NVarcharInterceptor implementing IDbCommandInterceptor, and overrode all of the ..Executing(..) methods to contain the following code:

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

这有效地从我的 DbContext 上执行的任何命令中删除任何不需要的 NVarchar 引用.

This effectively removes any of the unwanted NVarchar references, from any command being executed on my DbContext.

为了添加拦截器，我在 DBConfiguration 类中添加了以下代码:

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

this.AddInterceptor(new NVarcharInterceptor());

这篇关于ODP.Net 托管驱动程序 - ORA-12704:生成的代码中的字符集不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！