Case Insensitive Queries are not working

[shyam.pundkar]

Dear Support,

We have case insensitive oracle instance (NLS_COMP:LINGUISTIC,NLS_SORT:BINARY_CI). when we try to execute queries on it using devart oracle provider, we are unable to get result from case insensitive queries.
For example:
select * from "Test" where "Field1" like 'A%'

this query returns result for field1 values starting with 'a' as well as 'A' in SQL Plus but in the application using devart provider, it is returning just one value which is field value starting with A.

Please let me know you have any solution related to it.

Thanks
Shyam

[Pinturiccio]

We could not reproduce the issue with the latest version of dotConnect for Oracle. Please try setting (NLS_COMP:LINGUISTIC,NLS_SORT:BINARY_CI) before executing your query:

Code: Select all

OracleConnection conn = new OracleConnection("your connection string");
conn.Open();
OracleCommand comm = new OracleCommand("alter session set NLS_COMP=LINGUISTIC", conn);
comm.ExecuteNonQuery();
comm.CommandText = "alter session set NLS_SORT=BINARY_CI";
comm.ExecuteNonQuery();
comm.CommandText = "select * from \"Test\" where \"Field1\" like 'A%'";
OracleDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
    for (int i = 0; i < reader.FieldCount; i++)
        Console.Write(reader.GetValue(i) + "\t");
    Console.WriteLine();
}

[shyam.pundkar]

We are using 7.6.202.0.

We tried your approach but then we would have to execute these queries every time we make a connection to database as ours is a web application which use Entity Framework.

The solution we got to this is to set below properties
Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance.QueryOptions.CaseInsensitiveLike = true;
Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance.QueryOptions.CaseInsensitiveComparison = true;

Which solved the issue.

But can you confirm from your side whether its a right thing to use these properties?
Also let us know if you know a better solution to this problem.

Thanks
Shyam

[Shalex]

The solution we got to this is to set below properties

Code: Select all

Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance.QueryOptions.CaseInsensitiveLike = true;
Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance.QueryOptions.CaseInsensitiveComparison = true;

Which solved the issue.

But can you confirm from your side whether its a right thing to use these properties?

This is a correct approach for the Entity Framework applications.