Search query uses padded string which causes empty collection returned despite it shouldn't

[ElAdriano]

Problem:

For latest version of Oracle.EntityFrameworkCore lib, search string in generated sql query has unnecessary padding which results with empty collection of rows returned, despite the fact that in database there are rows that meet condition specified in a query.

Environment:

Lib version : 8.21.121 .NET SDK version: 8.0.201 Database : Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.3.0.0.0 (I'm connecting to existing one and don't run CreateDatabase)

Description:

Using this code

var context = new OracleContext();

string searchValue = "makot";
var query = context.SomeEntityRepo.Where(e => e.StringCol.Contains(searchValue));
List<SomeEntity> searchResult = await query.ToListAsync(); // <- searchResult is empty list after await

I receive empty list in 'searchResult' despite the fact that in database there's a record with 'StringCol'="Alamakota".

Even more surpring is what query is produced under the hood. When I attach with debugger right before query execution, in DebugView there's the following sql:

When executing produced query directly in the database I get empty result as well.

However, when the padding is a bit shorter (I removed 3 spaces from the padding), query actually returns matching records.

Expected behavior:

Don't add padding in produced query with 'LIKE' keyword at all.

Steps to recreate

Table DDL (create table in database):

CREATE TABLE "xxora"."SomeEntityRepo" 
(	
   "STRINGCOL" CHAR(24) DEFAULT ' ' NOT NULL ENABLE,
    CONSTRAINT "STRINGCOL_KEY" PRIMARY KEY ("STRINGCOL")
)

Full C# code (Program.cs):

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace FastCoding;

public class SomeEntity
{
    public virtual string StringCol { get; set; }

    public SomeEntity(string stringCol)
    {
        StringCol = stringCol;
    }

    public static void ConfigureEntity(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<SomeEntity>(entity =>
        {
            entity.HasKey(e => e.StringCol).HasName("STRINGCOL_KEY");
            entity.Property(e => e.StringCol).IsRequired().IsUnicode(false).HasMaxLength(24)
                                             .IsFixedLength().HasConversion(v => v.PadRight(24), v => v.TrimEnd())
                                             .ValueGeneratedNever();

            // Oracle db-specific configuration
            entity.Property(e => e.StringCol).HasColumnName("STRINGCOL").HasColumnType("CHAR(24)");
        });
    }
}

public class OracleContext : DbContext
{
    public DbSet<SomeEntity> SomeEntityRepo { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string connectionString = "**PUT HERE YOUR OWN CONNECTION STRING TO DB**";

        Action<string> loggingAction = (message) =>
        {
            File.AppendAllText("C:\\ORACLE_LIKE_query_bug.log", $"{message}\n");
        };

        optionsBuilder.UseOracle(connectionString, _ => { }).LogTo(loggingAction, LogLevel.Trace);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        SomeEntity.ConfigureEntity(modelBuilder);
    }
}

static class Program
{
    static async Task Main(string[] args)
    {
        var context = new OracleContext();

        /*
        // Use this code to create entity for created 'SomeEntity' table
        var entity = new SomeEntity("Alamakota");
        context.SomeEntityRepo.Add(entity);
        context.SaveChanges();
        */

        string searchValue = "makot";
        var query = context.SomeEntityRepo.Where(e => e.StringCol.Contains(searchValue));
        List<SomeEntity> searchResult = await query.ToListAsync();
    }
}                

*.csproj:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="8.0.2" />
    <PackageReference Include="Oracle.EntityFrameworkCore" Version="8.21.121" />
  </ItemGroup>

</Project>