Oracle Text Contains Function Mapping with EF Core 6

[cookiem3r]

I am attempting to map Oracle Text Contains function on an Index column using Entity Framework Core.

I am referencing this issue #136 using a similar set of codes. However, the query being generated does not seem to be able to run.

SELECT "t"."IDKEY", "t"."NAME" FROM "TB_PERSON" "t" where CASE WHEN "CONTAINS"("t"."NAME",N'TESTNAME') > 0 THEN 1 ELSE 0 END > 0

resulting in DRG-50901:text query parser syntax error on line1, column1

Versions: Oracle Entity Framework Core 6.21.5 Entity Framework Core 6.0.2

On a side note, I could not find any documentation/example of how the Oracle Text Contains Function can be mapped to Linq Where Clause with Entity Framework. Does anyone has any example?

[alexkeh]

Do you have a complete test case so that we can see how the LINQ generates the SQL?

[cookiem3r]

Below is the dummy class I recreated

using Microsoft.EntityFrameworkCore;
using System.Reflection;

namespace DatabaseEF
{

public class DatabaseCodes : DbContext
{
    public virtual DbSet<Person> People { get; set; } = null!;
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseOracle("...").LogTo(Console.WriteLine);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        MethodInfo containsMethodInfo = typeof(OracleDbFunctions).GetMethod(nameof(OracleDbFunctions.Contains))!;

        modelBuilder.Entity<Person>(entity =>
        {
            entity.HasKey(e => new { e.IdKey, e.Name }).HasName("...");
            entity.ToTable("TB_PERSON");
            entity.HasIndex(e => e.Name, "...");

            entity.Property(e => e.IdKey)
            .HasMaxLength(14)
            .IsUnicode(false)
            .ValueGeneratedOnAdd()
            .HasColumnName("IDKEY")
            .IsFixedLength();

            entity.Property(e => e.Name)
           .HasMaxLength(100)
           .IsUnicode(false)
           .ValueGeneratedOnAdd()
           .HasColumnName("NAME");
        });

        modelBuilder.HasDbFunction(containsMethodInfo).HasName("CONTAINS");
    }
}

public static class OracleDbFunctions
{
    public static int Contains(string column, string text) => throw new NotSupportedException();
}

public class Person
{
    public string IdKey { get; set; } = "";
    public string Name { get; set; } = "";
}
}

I call it using

var person = db.People.Where(x => OracleDbFunctions.Contains(x.Name, "TestName") > 0).ToList();

[alexkeh]

I was able to reproduce the problem, through I got a different error. Nonetheless, I've filed bug 34126553 for now to get some feedback from my dev team about this particular scenario (i.e. the recommended way to use CONTAINS) with Oracle EF Core and for further investigation.

[cookiem3r]

Is there any update/advise on how we can use CONTAINS with Oracle EF Core?

[tomasfil]

@foolnidiot You can do directly x => x.Name.Contains("TestName")

[alexkeh]

After some investigation, the ODP.NET team has found the root cause is a DB bug (35424066). This bug affects DB 19c, but not 23c, which is currently available as a Developer Release.

If you are using DB 19c or another DB version that this bug affects, there are two possible workarounds we've found:

1. As per the test case provided, which creates a normal index using the "HasIndex" Fluent API, try using C# string.Contains method instead of Oracle's CONTAINS function

var person = db.People.Where(x => x.Name.Contains("TestName")).ToList();

2. If you want to use Oracle CONTAINS function, you have to create an Oracle Text-specific role and index.. Oracle Text Index can be created using below syntax:

create index SAMPLEINDEX on TB_PERSON(NAME) indextype is ctxsys.context;

EF Core does not support creating the Oracle Text index using the "HasIndex" Fluent API. They have to be created separately on table columns in order to use Oracle Text's CONTAINS function. Then, apply below changes:

A. Use Boolean return type instead of Integer

public static class OracleDbFunctions
    {
      public static bool Contains(string column, string text) => throw new NotSupportedException();
    }

B. Change the LINQ where clause predicate:

var person = db.People.Where(x => OracleDbFunctions.Contains(x.Name, "Test Name")).ToList();

This will generate the SQL below that returns the result set without any error:

SELECT
    "t"."IDKEY",
    "t"."NAME"
FROM
    "TB_PERSON" "t"
WHERE
    "CONTAINS"("t"."NAME", N'Test Name') > 0;

[alexkeh]

Fixed in Oracle DB 23c with plans to fix bug (35424066) in DB 19c.