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>

[alexkeh]

Thanks for reporting, @ElAdriano! I was able to reproduce this issue with your test case using the latest EF Core 8 (8.0.4) and Oracle EF Core provider (8.21.14). I filed a bug (36560211) to investigate the root cause and to have it fixed.

[alexkeh]

@ElAdriano The Oracle EF Core team team analyzed the bug and the test case. It is using CHAR(24) data type and CHAR is fixed length data type. That means even if you insert “Alamakota” in the column, the actual data in the column has extra spaces.

This can be seen via below query- SQL> select STRINGCOL || 'a' from "SomeEntityRepo";

STRINGCOL||'A'
-------------------------
Alamakota               a

Now when you query, the following SQL is generated. SELECT "s"."STRINGCOL" FROM "SomeEntityRepo" "s" WHERE "s"."STRINGCOL" LIKE :p0 ESCAPE '\'

Since you use the conversion methodPadRight(24), it is causing the parameter value to be padded up to length 24.

So the parameter value :p0 becomes '%makot% ' (notice the extra spaces) If PadRight(24) conversion is not used, then the parameter value will be '%makot%' only and the query would return the expected result.

You also mentioned-

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

This is also expected since removing some padding spaces from the parameter would result in matching the condition.

Thus, the behavior you observe is expected and not a bug.

[ElAdriano]

@alexkeh

It is using CHAR(24) data type and CHAR is fixed length data type. That means even if you insert “Alamakota” in the column, the actual data in the column has extra spaces.

I'm aware of it and that's what I wanted to achieve. I want to use full possible length for that one specific column.

Since you use the conversion method PadRight(24), it is causing the parameter value to be padded up to length 24. So the parameter value :p0 becomes '%makot% ' (notice the extra spaces) If PadRight(24) conversion is not used, then the parameter value will be '%makot%' only and the query would return the expected result.

Pardon me, but I don't see any reason why search string should be padded up to given length when filtering objects. No matter what is stored in given column - 'Alamakota' (without padding) or 'Alamakota ' (with padding) - both should return expected results because both contain search string ('makot').

Hence, this piece of code:

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

should return all matching rows - it doesn't matter whether 'Alamakota' is saved with padding or not. Don't You agree?

[lwestfall]

I've run into similar issues in the past using an ERP that stores data in an Oracle DB with padding, and I similarly use a value converter to handle these, but you have to be very careful when filtering on strings which results in wildcard queries (Contains, StartsWith, EndsWith, etc.).

'%makot% ' (17 trailing whitespace chars) won't match 'Alamakota ' (15 trailing whitepace chars). This will only work when the count of whitespace characters happens to match exactly (i.e. when your wildcard characters match with exactly 1 more character).

What you can do for these types of clauses is skip the value conversion by casting your property to object and then back to string like so. This should result in a query like WHERE "s"."STRINGCOL" LIKE '%makot%' ESCAPE '' (note the lack of padding, which you want in this case).

var query = context.SomeEntityRepo.Where(e => ((string)(object)e.StringCol).Contains(searchValue));

Some more context here: https://stackoverflow.com/a/71705306/6621862

[alexkeh]

@ElAdriano In the Oracle dev team investigation of this issue, we observe that PadRight(24) in the entity configuration elicits the parameter padding in the Microsoft EF Core Relational layer, not in Oracle EF Core provider nor ODP.NET.

Oracle tested the same setup with SQL Server and we observe the same space padding behavior.

[ElAdriano]

@lwestfall Thank You for the workaround - it's good to know that there's a way to make it work until actual issue is fixed. However, I think this shouldn't be the way to solve this - provided lib should handle it in such wildcard queries. @alexkeh I will report this issue to Microsoft as well :)

[lwestfall]

No problem - however I don't think I agree. The issue is that value converters will always intercept your parameters when building the SQL (without the casting method). This is very intentional behavior and desired in nearly every case - excluding certain specific cases like this. In other words this very much isn't a bug - it's a feature. I don't think you will get very far trying to present it as such to Microsoft.

That said, the API could certainly be improved for ignoring value converters as the casting method is neither intuitive or very well documented. If you proposed a better API to ignore value conversion, I'd be the first to upvote it.

[alexkeh]

Closing issue as resolution requires a MS software change, bug fix, or is expected behavior.