Devart Forums

Posted: **Sun 17 Dec 2017 02:58**

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.

Posted: **Wed 20 Dec 2017 06:37**

We are processing your request.

Posted: **Tue 26 Dec 2017 15:25**

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

Posted: **Tue 02 Jan 2018 18:37**

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

Posted: **Wed 03 Jan 2018 21:16**

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.

Posted: **Thu 04 Jan 2018 10:09**

Thank you for the additional information.

Posted: **Thu 11 Jan 2018 20:54**

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.

Devart Forums

case insensitive search

case insensitive search

Re: case insensitive search

Re: case insensitive search

Re: case insensitive search

Re: case insensitive search

Re: case insensitive search

Re: case insensitive search