EntityFramework and oracle Contain in a Where clause not working with a variable

Question

I'm using ASP.NET MVC 4.5.2, entityframework 6.1.3 and Oracle 19c (19.3.0) database. I encounter a strange behavior with an EntityFramework request.

With the following request:

List<College> testQuery1 = unitOfWork.GetRepository<College>()
                                     .AsQueryable()
                                     .Where(college => college.Nom.Contains("A"))
                                     .ToList();

I have the following result => testQuery1.Count = 172. But with this request:

string queryParameter = "A";

List<College> testQuery2 = unitOfWork.GetRepository<College>()
                                     .AsQueryable()
                                     .Where(college => college.Nom.Contains(queryParameter))
                                     .ToList();

the result is testQuery2.Count = 0. This problem happens only with my Oracle 19.3.0.0 database, with Oracle 19.10.0.0 or 12.1.2.0 everything works fine, there is no discrepency. Here are the queries genetated by entityframework. For testQuery1:

SELECT 
"Extent1"."ID" AS "ID", 
"Extent1"."ID_LIEU_RDV" AS "ID_LIEU_RDV", 
"Extent1"."ID_ADRESSE" AS "ID_ADRESSE", 
"Extent1"."DSIT_NOSITE" AS "DSIT_NOSITE", 
"Extent1"."CODE_COLLEGE" AS "CODE_COLLEGE", 
"Extent1"."NOM" AS "NOM", 
"Extent1"."CREATED_BY" AS "CREATED_BY", 
"Extent1"."CREATED_DATE" AS "CREATED_DATE", 
"Extent1"."MODIFIED_BY" AS "MODIFIED_BY", 
"Extent1"."MODIFIED_DATE" AS "MODIFIED_DATE"
FROM "DSS"."DSS_COLLEGE" "Extent1"
WHERE ("Extent1"."NOM" LIKE '%A%')

and for testQuery2:

SELECT 
"Extent1"."ID" AS "ID", 
"Extent1"."ID_LIEU_RDV" AS "ID_LIEU_RDV", 
"Extent1"."ID_ADRESSE" AS "ID_ADRESSE", 
"Extent1"."DSIT_NOSITE" AS "DSIT_NOSITE", 
"Extent1"."CODE_COLLEGE" AS "CODE_COLLEGE", 
"Extent1"."NOM" AS "NOM", 
"Extent1"."CREATED_BY" AS "CREATED_BY", 
"Extent1"."CREATED_DATE" AS "CREATED_DATE", 
"Extent1"."MODIFIED_BY" AS "MODIFIED_BY", 
"Extent1"."MODIFIED_DATE" AS "MODIFIED_DATE"
FROM "DSS"."DSS_COLLEGE" "Extent1"
WHERE ("Extent1"."NOM" LIKE :p__linq__0 ESCAPE '\')

where p_linq_0 is equal to "%A%". If I execute these queries directly on the database, the result is the same, 172 results both times.

It happens if I replace Contains by StartsWith and EndsWith too. Do you have any idea where this strange behavior may come from ?

Thanks in advance for any insights.

Answer 1

We finally found what was wrong. I share it with you in case someone encouter the same problem.

After the creation of our 19.3.0 Oracle database, the NLS parameter "NLS_CHARACTERSET" had a wrong value and was changed. It was not a good idea as changing NLS parameters after the database creation is not advised by Oracle and it was the origine of our problem. The solution is, as you can imagine to roll back to the old value or create another database with the right value and migrate datas