EF Core 6.0 - Invalid column mapping for nchar(N) resulting in data corruption

[troglas]

Problem: Oracle incorrectly maps the nchar field into char when creating DbCommand. This results in character corruption on add/update. I connect to existing table. I don't run CreateDatabase using this codefirst

var db = new NcharContext(); var entity = new B1TEST(); entity.Mynchar = "ąćłż"; // Some non ansi characters db.B1TestRepo.Add(entity); db.SaveChanges();

Adding dbtype hint to Entity Property fluent api does not solve the problem.

.HasColumnType("NCHAR(4)"); 

Version: of the nuget: Oracle.EntityFrameworkCore 6.21.100 using dotnet 6.0

Table DDL:

CREATE TABLE "XXORA"."B1TEST" 
(   
   "MYNCHAR" NCHAR(4) DEFAULT ' ' NOT NULL ENABLE,
    CONSTRAINT "B11TEST_PRIM_KEY" PRIMARY KEY ("MYNCHAR")
)

Full Example code (Program.cs)

using System.Data.Common;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace DbNcharTest;

public class OracleCommandInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
    {
        return result;
    }

    public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
    {
        return result;
    }

    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        return result;
    }
}

public class B1TEST
{
    /// <summary>
    /// Unicode TEST of NCHAR(4)
    /// </summary>
    public virtual String Mynchar { get; set; }
}

public class NcharContext : DbContext
{
    public DbSet<B1TEST> B1TestRepo { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseOracle(
            "Data Source=somedbconnectionstring",
            _ =>
            {
                optionsBuilder.AddInterceptors(new List<IInterceptor>{new OracleCommandInterceptor()});
            });
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<B1TEST>(entity =>
        {
            entity.HasKey(e=>e.Mynchar).HasName("B1TEST_prim_key");
            entity.Property(e=>e.Mynchar).IsRequired().IsUnicode(true).HasMaxLength(4).IsFixedLength();
            // entity.Property(e=>e.Mynchar).HasColumnType("NCHAR(4)"); // this does not work.

            entity.ToTable("B1TEST");
            entity.Property(e=>e.Mynchar).HasColumnName("MYNCHAR");
        });
    }
}

static class Program
{
    static void Main(string[] args)
    {
        var db = new NcharContext();
        var entity = new B1TEST();
        entity.Mynchar = "ąćłż";

        db.B1TestRepo.Add(entity);
        db.SaveChanges();
    }
}

csproj:

<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net6.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
    </PropertyGroup>
    <ItemGroup>
        <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.22"/>
        <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.22"/>
        <PackageReference Include="Oracle.EntityFrameworkCore" Version="6.21.110"/>
        <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="6.0.22"/>
    </ItemGroup>
</Project>

This is a workaround (for insert/update at least)

public override InterceptionResult<DbDataReader> ReaderExecuting(
            DbCommand command,
            CommandEventData eventData,
            InterceptionResult<DbDataReader> result)
        {
            foreach (var param in command.Parameters)
            {
                var oparam = (Oracle.ManagedDataAccess.Client.OracleParameter)param;
                if (oparam.DbType == DbType.StringFixedLength)
                {
                    // nchar
                    oparam.OracleDbType = OracleDbType.NChar;
                }
            }

            return result;
        }

[alexkeh]

I was able to run your sample app with Oracle EF Core 6.21.110 successfully both with and without the following code line commented out.

entity.Property(e=>e.Mynchar).HasColumnType("NCHAR(4)");

I used DB 21c. Which DB are you using?

[troglas]

I am using: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.3.0.0.0

However, it's not the case of the database, the generated Oracle Command has invalid parameter type for nchar column. See the screen I supplied, the value in the OracleDbType is Char(4) while it should be Nchar(4). This code I supplied executes and does not throw any exception but it stores corrupted data in the Mynchar column.

When I enforce correct OracleDbType to oparam.OracleDbType = OracleDbType.NChar; using command interceptor , it works. However I should not be forced to use interceptors to fix this parameter type.

[alexkeh]

When I run the app, the data inserted doesn't appear corrupted.

[troglas]

I find Your result strange. The only explanation I can think of is, Your database has maybe a nls setting so the Polish Characters are already handled. Could You try to use Japanese chars like ゞゃはづ (4 characters max) and see in debug the oracle command. OracleDbType of the DbParameter that is sent to database ? As You see in the screen I provided, the CHAR is used, while it should be NCHAR.

I debugged some more in the Oracle.ManagedDataAccess.Client code. (3.21.110) that is used by efcore when constructing parameter. I saw this:

var param = new OracleParameter(); // default on construction is OracleDbType = varchar2
param.DbType = DbType.StringFixedLength: // after this line the param.OracleDbType = OracleDbType.Char (!). As char is non-unicode the data loss will occur.

I though the mapping would be: DbType.StringFixedLength -> NCHAR DbType.AnsiStringFixedLength -> CHAR DbType.String ->NVARCHAR DbType.AnsiString -> VARCHAR

I looked at the Oracle Docs (https://docs.oracle.com/en/database/oracle/oracle-database/23/odpnt/featOraCommand.html#GUID-F308270A-42B6-48AC-B6DB-C9ED2AE55B93) and I see that StringFixedLength is used for both NCHAR and CHAR (is this really the case?) , in our environment the OracleDbType.Char saves the value as ???? into datarbase instead of real value ゞゃはづ. When I change the OracleDbType to NCHAR it saves correctly.

On microsoft page there is nchar->stringfixedlength and char -> ansistringfixedlength specified for ADO.net: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/oracle-data-type-mappings

I am confused to be honest. Even more confused as You say it works on Your environment. Are You able to reproduce this in 19c ?

[alexkeh]

Using Japanese characters saves data to the DB in the same app without corruption. I do see CHAR is being used instead of NCHAR. Let me consult one of my colleagues who knows more about NLS than I.

[alexkeh]

This is bug 35958423. It has been fixed in the main ODP.NET code line. We plan to port the fix to the next Oracle EF Core 6 and 7 releases, though it's likely to appear first in the Oracle EF Core 8 release of 21.12.1.

[alexkeh]

Fixed with Oracle EF Core 8