Invalid ansi string fixed length Parameter value passed to query when server side select includes parameter

[troglas]

Hi Oracle Team,

Invalid query parameter value is passed to query when server side select contains the filter value and some other column from entity.
See that the filter query is run on char(80) column so the parameter value should be passed to the query like

example:

// Prepare data
var db = new NcharContext();
var entity = new B30TEST();
entity.Tschar = "ts";
entity.Index = 666;
db.B30TestRepo.Add(entity);
db.SaveChanges();

// Test
var result = db.B30TestRepo.Where(x => x.Tschar == entity.Tschar).Select(x => new { Val = entity.Tschar, val2 = x.Tsbool }) .ToList();
Debug.Assert(result.Count == 1); // This fails! - record not found

Problem
The filter column type is char(80) and the parameter value passed is

Parameters=[:entity_Tschar_0='ts' (Size = 2000)]

Looking at the log I see this:

 11/24/2023 14:51:17.741 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (2ms) [Parameters=[:entity_Tschar_0='ts' (Size = 2000)], CommandType='Text', CommandTimeout='0']
      SELECT :entity_Tschar_0 "Val", "b"."TSBOOL" "val2"
      FROM "B30TEST" "b"
      WHERE "b"."TSCHAR" = :entity_Tschar_0

Expected:
while the parameter value should be

:  :entity_Tschar_0='ts                                                                               '

Full Code Example and db definition of the table

 CREATE TABLE "XXORA"."B30TEST" 
   (	"MA_INDEX" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE, 
	"TSBOOL" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE, 
	"TSSHORT" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE, 
	"TSINT" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE, 
	"TSFLOAT" FLOAT(63) DEFAULT 0 NOT NULL ENABLE, 
	"TSDOUBLE" FLOAT(126) DEFAULT 0 NOT NULL ENABLE, 
	"TSLONG" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE, 
	"TSCHAR" CHAR(80 BYTE) DEFAULT ' ' NOT NULL ENABLE, 
	"TSNCHAR" NCHAR(80) DEFAULT ' ' NOT NULL ENABLE, 
	"TSVARCHAR" VARCHAR2(200 BYTE) DEFAULT NULL, 
	"TSNVARCHAR" NVARCHAR2(200) DEFAULT NULL, 
	"TSDATE" DATE DEFAULT '1900-01-01 00:00:00' NOT NULL ENABLE, 
	"TSTIME" DATE DEFAULT '1900-01-01 00:00:00' NOT NULL ENABLE, 
	"TSBLOB" BLOB DEFAULT NULL, 
	"MAROWVER" NUMBER(*,0) DEFAULT 1, 
	 CONSTRAINT "B30TEST_PRIM_KEY" PRIMARY KEY ("MA_INDEX")
)

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="Oracle.EntityFrameworkCore" Version="6.21.110"/>
        <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="6.0.22"/>
    </ItemGroup>
</Project>

using System.Diagnostics;
using Microsoft.EntityFrameworkCore;

namespace FixedLengthCharFilter;

public partial class B30TEST
{
    public virtual int RowVersion { get; set; }

    public virtual Int32 Index { get; set; }

    /// <summary>
    /// TEST of BOOLEAN
    /// </summary>
    public virtual Int16 Tsbool { get; set; }

    /// <summary>
    /// TEST of SHORT
    /// </summary>
    public virtual Int16 Tsshort { get; set; }

    /// <summary>
    /// TEST of INT
    /// </summary>
    public virtual Int32 Tsint { get; set; }

    /// <summary>
    /// TEST of FLOAT
    /// </summary>
    public virtual Single Tsfloat { get; set; }

    /// <summary>
    /// TEST of DOUBLE
    /// </summary>
    public virtual Decimal Tsdouble { get; set; }

    /// <summary>
    /// TEST of LONG
    /// </summary>
    public virtual Int32 Tslong { get; set; }

    /// <summary>
    /// TEST of CHAR
    /// </summary>
    public virtual String Tschar { get; set; }

    /// <summary>
    /// TEST of NCHAR
    /// </summary>
    public virtual String Tsnchar { get; set; }

    /// <summary>
    /// TEST of VARCHAR/VARCHAR2
    /// </summary>
    public virtual String Tsvarchar { get; set; }

    /// <summary>
    /// TEST of NVARCHAR/NVARCHAR2
    /// </summary>
    public virtual String Tsnvarchar { get; set; }

    /// <summary>
    /// TEST of DATE/DATETIME
    /// </summary>
    public virtual DateTime Tsdate { get; set; }

    /// <summary>
    /// TEST of DATE/DATETIME Time with only HH:MM:SS
    /// </summary>
    public virtual DateTime Tstime { get; set; }

    /// <summary>
    /// TEST of BLOB/VARBINARY
    /// </summary>
    public virtual Byte[] Tsblob { get; set; }

    public B30TEST()
    {
        Index = 0;
        Tsbool = 0;
        Tsshort = 0;
        Tsint = 0;
        Tsfloat = 0;
        Tsdouble = 0;
        Tslong = 0;
        Tschar = " ";
        Tsnchar = " ";
        Tsdate = new DateTime(1900, 1, 1, 0, 0, 0);
        Tstime = new DateTime(1900, 1, 1, 0, 0, 0);
    }
}

public class NcharContext : DbContext
{
    public DbSet<B30TEST> B30TestRepo { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseOracle(
            "Data Source=YourDatabaseConnection",
            _ =>
            {
                optionsBuilder.LogTo(Console.WriteLine).EnableDetailedErrors().EnableSensitiveDataLogging();
            });
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        
        
        
        modelBuilder.Entity<B30TEST>(entity =>
        {
            entity.Property(e => e.RowVersion).IsRequired().IsConcurrencyToken().ValueGeneratedOnAddOrUpdate();
            entity.HasKey(e=>e.Index).HasName("B30TEST_prim_key");
            entity.Property(e=>e.Index).IsRequired().ValueGeneratedNever();
            entity.Property(e=>e.Tsbool).IsRequired();
            entity.Property(e=>e.Tsshort).IsRequired();
            entity.Property(e=>e.Tsint).IsRequired();
            entity.Property(e=>e.Tsfloat).IsRequired();
            entity.Property(e=>e.Tsdouble).IsRequired();
            entity.Property(e=>e.Tslong).IsRequired();
            entity.Property(e=>e.Tschar).IsRequired().IsUnicode(false).HasMaxLength(80).IsFixedLength().HasConversion(v => v.PadRight(80), v => v.TrimEnd());
            entity.Property(e=>e.Tsnchar).IsRequired().IsUnicode(true).HasMaxLength(80).IsFixedLength().HasConversion(v => v.PadRight(80), v => v.TrimEnd());
            entity.Property(e=>e.Tsvarchar).IsUnicode(false).HasMaxLength(200);
            entity.Property(e=>e.Tsnvarchar).IsUnicode(true).HasMaxLength(200);
            entity.Property(e=>e.Tsdate).IsRequired();
            entity.Property(e=>e.Tstime).IsRequired();
            entity.Property(e=>e.Tsblob);
        });
        
        modelBuilder.Entity<B30TEST>(entity =>
        {
            entity.ToTable("B30TEST");
            entity.Property(e => e.RowVersion).HasColumnName("MAROWVER");
            entity.Property(e=>e.Index).HasColumnName("MA_INDEX");
            entity.Property(e=>e.Tsbool).HasColumnName("TSBOOL");
            entity.Property(e=>e.Tsshort).HasColumnName("TSSHORT");
            entity.Property(e=>e.Tsint).HasColumnName("TSINT");
            entity.Property(e=>e.Tsfloat).HasColumnName("TSFLOAT");
            entity.Property(e=>e.Tsdouble).HasColumnName("TSDOUBLE").HasColumnType("FLOAT(126)");
            entity.Property(e=>e.Tslong).HasColumnName("TSLONG");
            entity.Property(e=>e.Tschar).HasColumnName("TSCHAR").HasColumnType("CHAR(80)");
            entity.Property(e=>e.Tsnchar).HasColumnName("TSNCHAR").HasColumnType("NCHAR(80)");
            entity.Property(e=>e.Tsvarchar).HasColumnName("TSVARCHAR");
            entity.Property(e=>e.Tsnvarchar).HasColumnName("TSNVARCHAR");
            entity.Property(e=>e.Tsdate).HasColumnName("TSDATE");
            entity.Property(e=>e.Tstime).HasColumnName("TSTIME");
            entity.Property(e=>e.Tsblob).HasColumnName("TSBLOB");
        });
        
        
    }
}

static class Program
{
    static void Main(string[] args)
    {
        var db = new NcharContext();
        var entity = new B30TEST();
        entity.Tschar = "ts";
        entity.Index = 666;
        db.B30TestRepo.Add(entity);
        db.SaveChanges();
  
        //var result2= db.B30TestRepo.Where(x => x.Tschar == entity.Tschar).Select(x => new { Val = entity.Tschar })
        //    .ToList();
        // Debug.Assert(result2.Count == 1); // This works!
        
        var result = db.B30TestRepo.Where(x => x.Tschar == entity.Tschar).Select(x => new { Val = entity.Tschar, val2 = x.Tsbool })
            .ToList();
        Debug.Assert(result.Count == 1); // This fails! - record not found
    }
    
}

[alexkeh]

Thanks @troglas for reporting and the complete test case. I've been able to reproduce your error and filed a bug (36045692) for the Oracle EF Core team to review and resolve.

[troglas]

The same problem is with nchar (stringFixedLength)

example: (same main code as in description)

var entity = new B30TEST();
entity.Tschar = "ts";
entity.Index = 667;
entity.Tsnchar = "tsn";
db.B30TestRepo.Add(entity);
db.SaveChanges();

var result = db.B30TestRepo.Where(x => x.Tsnchar == entity.Tsnchar).Select(x => new { Val = entity.Tsnchar, val2 = x.Tsbool }).ToList();
Debug.Assert(result.Count == 1); // This fails! - record not found

Log output

dbug: 11/26/2023 14:45:12.396 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[:entity_Tsnchar_0='tsn' (Size = 2000)], CommandType='Text', CommandTimeout='0']
      SELECT :entity_Tsnchar_0 "Val", "b"."TSBOOL" "val2"
      FROM "B30TEST" "b"
      WHERE "b"."TSNCHAR" = :entity_Tsnchar_0
info: 11/26/2023 14:45:12.446 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (50ms) [Parameters=[:entity_Tsnchar_0='tsn' (Size = 2000)], CommandType='Text', CommandTimeout='0']
      SELECT :entity_Tsnchar_0 "Val", "b"."TSBOOL" "val2"
      FROM "B30TEST" "b"
      WHERE "b"."TSNCHAR" = :entity_Tsnchar_0

while the :entity_Tsnchar_0 value should be:

dbug: 11/26/2023 14:46:49.726 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[:entity_Tsnchar_0='tsn                                                                             ' (Size = 80) (DbType = StringFixedLength)], CommandType='Text', CommandTimeout='0']
      SELECT 1
      FROM "B30TEST" "b"
      WHERE "b"."TSNCHAR" = :entity_Tsnchar_0

[alexkeh]

Thanks! We'll address both issues in the same bug.

[alexkeh]

@troglas

The ODP.NET dev team has completed their investigation. This bug is not reproducible with Oracle EF Core 7 NuGet (7.21.8 - 7.21.12) and also not reproducible with the internal Oracle EF Core 8 version we have.

We can replicate this issue with Oracle EF Core 6 NuGet (6.21.120). This is a relational layer issue in EF Core 6, in which parameter names are getting reused based on the variable names used in LINQ. Due to that, the type mapping of a parameter gets evaluated only once (in the customer case, entity_Tschar_0 is evaluated as .NET string type mapping (VARCHAR2) but the WHERE condition requires the parameter to be evaluated as CHAR type mapping which is why the query does not return any data).

This issue got resolved in EF core 6 as a fix of reusing the same parameter in query: dotnet/efcore#23271

But it also introduces another issue like what you reported in which different parameter names are required if same variable is used in LINQ.

So MS again improved this fix in EF Core 7:
dotnet/efcore#22524

We suggest using below workaround for EF Core 6 version or upgrade to Oracle EF Core 7 or EF Core 8 versions:

• Create one more variable with different name and then use it in the LINQ

Workaround Example:

// Prepare data
var db = new NcharContext();
var entity = new B30TEST();
entity.Tschar = "ts";
entity.Index = 666;
db.B30TestRepo.Add(entity);
db.SaveChanges();

// create one more variable with same value as entity.Tschar and use it in LINQ select expression
var newTschar = "ts";
// Test
var result = db.B30TestRepo.Where(x => x.Tschar == entity.Tschar).Select(x => new { Val = newTschar, val2 = x.Tsbool }) .ToList();
Debug.Assert(result.Count == 1);