case insensitive search

[mrpeanut]

I'm using dotConnect for Oracle 9.5.399

I'd like to run a case insensitive 'where in' and a case insensitive 'like'

I've configured my OracleEntityProviderConfig instance like this.

Code: Select all

OracleEntityProviderConfig config = OracleEntityProviderConfig.Instance;
config.QueryOptions.CaseInsensitiveComparison = true;
config.QueryOptions.CaseInsensitiveLike = true;

And when I evaluate those properties as I'm running my queries they are both still true.

But when I execute a query using my DbSet expts like

Code: Select all

expts.Where(expt => param.Crops.Contains(expt.ExptCrop));

or

Code: Select all

expts.Where(e => e.ExptName.Contains(name));

I get case sensitive results.

Am I missing something really obvious about setting the QueryOptions?

Any help is appreciated.

[Shalex]

We are processing your request.

[Shalex]

The results of our test are the following:

Code: Select all

    var monitor = new Devart.Data.Oracle.OracleMonitor() { IsActive = true };

    var config = OracleEntityProviderConfig.Instance;
    config.QueryOptions.CaseInsensitiveComparison = true;
    config.QueryOptions.CaseInsensitiveLike = true;

    string name = "development"; // actual value is "Development"

    var model = new CSCOTTModel.CSCOTTEntities();

    // case 1
    var result = model.DEPTs.Where(e => name.Contains(e.DNAME)).ToList(); // result.Count=0

    // case 2
    var result2 = model.DEPTs.Where(e => e.DNAME.Contains(name)).ToList(); // result2.Count=1

The generated SQL:

Code: Select all

// case 1
SELECT 
"Extent1".DEPTNO,
"Extent1".DNAME,
"Extent1".LOC
FROM C##SCOTT.DEPT "Extent1"
WHERE :p__linq__0 LIKE '%' || "Extent1".DNAME || '%'

// case 2
SELECT 
"Extent1".DEPTNO,
"Extent1".DNAME,
"Extent1".LOC
FROM C##SCOTT.DEPT "Extent1"
WHERE UPPER("Extent1".DNAME) LIKE UPPER(:p__linq__0) ESCAPE '/'

Case 1: we confirm the issue, and will notify you when it is fixed.

Case 2: the code works as expected in our environment. Please specify:
a) the generated SQL in your environment
b) the version (x.x.x) of your Entity Framework
c) the versions of your Oracle Server and Oracle Client

[mrpeanut]

Sorry about the delay, I was out over the holidays.

Just in case it changes anything, I'm explicitly mapping columns like this:

Code: Select all

modelBuilder.Entity<ExptDto>()
  .ToTable("ATF_EXPT_PRISM")
  .HasKey(e => e.ExptId);
            
modelBuilder.Entity<ExptDto>()
  .Property(e => e.Crop)
  .HasColumnName("EXPT_CROP")
  .HasMaxLength(50);
modelBuilder.Entity<ExptDto>()
  .Property(e => e.Description)
  .HasColumnName("EXPT_DESCRIPTION");
modelBuilder.Entity<ExptDto>()
  .Property(e => e.ExptId)
  .HasColumnName("EXPT_ID");
modelBuilder.Entity<ExptDto>()
  .Property(e => e.Name)
  .HasColumnName("EXPT_NAME")
  .HasMaxLength(60);
modelBuilder.Entity<ExptDto>()
  .Property(e => e.Year)
  .HasColumnName("EXPT_YEAR")
  .HasMaxLength(50);

My queries now look like this.

Code: Select all

var monitor = new Devart.Data.Oracle.OracleMonitor() { IsActive = true };

var config = OracleEntityProviderConfig.Instance;
config.QueryOptions.CaseInsensitiveComparison = true;
config.QueryOptions.CaseInsensitiveLike = true;

string comparisonString = "corn";  // actual value is CORN
var comparisonQuery = _ctx.Experiments
  .Where(e => e.Crop == comparisonString).ToList();

string likeString = "asr"; // actual value is ASR
var likeQuery = _ctx.Experiments
  .Where(e => e.Name.Contains(likeString)).ToList();

But I'm still getting case sensitive results.

For Case 2 point (a)
For the comparison query

Code: Select all

SELECT "e".EXPT_ID, "e".EXPT_CROP, "e".EXPT_DESCRIPTION, "e".EXPT_NAME, "e".EXPT_YEAR
FROM TTRS.ATF_EXPT_PRISM "e"
WHERE "e".EXPT_CROP = :p__comparisonString_0

And for the like query

Code: Select all

SELECT "e".EXPT_ID, "e".EXPT_CROP, "e".EXPT_DESCRIPTION, "e".EXPT_NAME, "e".EXPT_YEAR
FROM TTRS.ATF_EXPT_PRISM "e"
WHERE INSTR("e".EXPT_NAME, :p__likeString_0) > 0

for Case 2 (b)
We don't have an explicit dependency on Entity framework, so probably just what is inherited from Devart.Data.Oracle.EFCore (9.5.399) which I think is Microsoft.EntityFrameworkCore.Relational (2.0.0)

for Case 2 (c)
We are using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
And I'm not really sure about the client, I want to say Devart dotConnect for Oracle 9.5.399.0 but I'm don't know exactly what you are asking for there.

And just to be very specific, we are running this targeting both net461 and netcoreapp2.0.

Let me know if you have any more questions

[mrpeanut]

If I remove the HasMaxLength(..) statement, the comparison query becomes case insensitive.

Code: Select all

SELECT "e".EXPT_ID, "e".EXPT_CROP, "e".EXPT_DESCRIPTION, "e".EXPT_NAME, "e".EXPT_YEAR
FROM TTRS.ATF_EXPT_PRISM "e"
WHERE DBMS_LOB.COMPARE(UPPER("e".EXPT_CROP), UPPER(:p__comparisonString_0)) = 0

I thought that might be useful for you to use while debugging, but doesn't it fix my problem.

[Shalex]

Thank you for the additional information.

[Shalex]

New build of dotConnect for Oracle 9.5.429 includes improvements:
* The bug with LIKE (.StartsWith/.Contains./EndsWith) is fixed
* The bug with case-insensitive comparison is fixed

Refer to viewtopic.php?f=1&t=36449.