dotConnect for Oracle CaseInsensitiveLike Problem

[GrMikeD]

Hi,

DotConnect for Oracle seems to ignore CaseInsensitiveLike setting since version 7.7x.

[Shalex]

Thank you for your report. We have reproduced the problem. We will post here when it is fixed.

[Shalex]

The CaseInsensitiveLike setting works with the latest (7.8.287) build of dotConnect for Oracle.

You can set it either in the code:

Code: Select all

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

or in app.config:

Code: Select all

<configuration>
  <configSections>
...
    <section name="Devart.Data.Oracle.Entity" type="Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfigurationSection, Devart.Data.Oracle.Entity, Version=7.8.287.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
  </configSections>
  <Devart.Data.Oracle.Entity xmlns="http://devart.com/schemas/Devart.Data.Oracle.Entity/1.0">
    <QueryOptions CaseInsensitiveLike="true"/>
  </Devart.Data.Oracle.Entity>
...
</configuration>

An example of LINQ to Entities for checking the CaseInsensitiveLike functionality:

Code: Select all

var query = context.DEPTs.Where(d => d.LOC.StartsWith("new")).ToList();

If dotConnect for Oracle v 7.8.287 doesn't work in your environment, please send us a small test project with the corresponding DDL/DML script for reproducing.

[GrMikeD]

Hi, I tested with latest v7.8.301 but problem persists.

Our application uses EF layer that is db agnostic and we initialize dotConnect through reflection. It used to work well until at least v6.50

Till I create a more complete example, I send you the initialization code

Thanx in advance!

Code: Select all

        //OracleEntityProviderConfig config = OracleEntityProviderConfig.Instance;
        //config.QueryOptions.NoEscapeLike = true;
        //config.QueryOptions.CaseInsensitiveComparison = true;
        //config.QueryOptions.CaseInsensitiveLike = true;
        //config.DmlOptions.BatchUpdates.Enabled = true;
        //config.DmlOptions.BatchUpdates.AsynchronousBatch = true;
        //config.DmlOptions.InsertNullBehaviour = InsertNullBehaviour.Omit;
        private void InitOracle()
        {
#pragma warning disable 612, 618
            Assembly providerAssembly = Assembly.LoadWithPartialName("Devart.Data.Oracle.Entity");
#pragma warning restore 612, 618

            Type oracleEntityProviderConfigType = providerAssembly.GetType("Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig");
            Type oracleEntityProviderDmlConfigType = providerAssembly.GetType("Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderDmlConfig");
            Type oracleEntityProviderBatchUpdateConfigType = providerAssembly.GetType("Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderBatchUpdateConfig");
            Type insertNullBehaviourType = providerAssembly.GetType("Devart.Data.Oracle.Entity.Configuration.InsertNullBehaviour");

            PropertyInfo oracleEntityProviderConfigProperty = oracleEntityProviderConfigType.GetProperty("Instance", BindingFlags.Static | BindingFlags.Public);

            PropertyInfo queryOptionsProperty = oracleEntityProviderConfigProperty.PropertyType.GetProperty("QueryOptions");
            PropertyInfo noEscapeLikeProperty = queryOptionsProperty.PropertyType.GetProperty("NoEscapeLike");
            PropertyInfo caseInsensitiveComparisonProperty = queryOptionsProperty.PropertyType.GetProperty("CaseInsensitiveComparison");
            PropertyInfo caseInsensitiveLikeProperty = queryOptionsProperty.PropertyType.GetProperty("CaseInsensitiveLike");

            PropertyInfo dmlOptionsProperty = oracleEntityProviderConfigProperty.PropertyType.GetProperty("DmlOptions");
            PropertyInfo batchUpdatesProperty = dmlOptionsProperty.PropertyType.GetProperty("BatchUpdates");
            PropertyInfo enabledProperty = batchUpdatesProperty.PropertyType.GetProperty("Enabled");
            PropertyInfo asynchronousBatchProperty = batchUpdatesProperty.PropertyType.GetProperty("AsynchronousBatch");
            PropertyInfo insertNullBehaviourProperty = dmlOptionsProperty.PropertyType.GetProperty("InsertNullBehaviour");

            object oracleEntityProviderConfigObject = oracleEntityProviderConfigProperty.GetValue(null, null);
            object queryOptionsObject = queryOptionsProperty.GetValue(oracleEntityProviderConfigObject, null);
            object dmlOptionsObject = dmlOptionsProperty.GetValue(oracleEntityProviderConfigObject, null);
            object batchUpdatesObject = batchUpdatesProperty.GetValue(dmlOptionsObject, null);
            object omitInsertNullBehaviourEnum = Enum.ToObject(insertNullBehaviourType, 3);

            noEscapeLikeProperty.SetValue(queryOptionsObject, true, null);
            caseInsensitiveComparisonProperty.SetValue(queryOptionsObject, true, null);
            caseInsensitiveLikeProperty.SetValue(queryOptionsObject, true, null);
            enabledProperty.SetValue(batchUpdatesObject, true, null);
            asynchronousBatchProperty.SetValue(batchUpdatesObject, true, null);
            insertNullBehaviourProperty.SetValue(dmlOptionsObject, omitInsertNullBehaviourEnum, null);
        }

[Shalex]

Code: Select all

        //OracleEntityProviderConfig config = OracleEntityProviderConfig.Instance;
        //config.QueryOptions.NoEscapeLike = true;
        //config.QueryOptions.CaseInsensitiveComparison = true;
        //config.QueryOptions.CaseInsensitiveLike = true;
        //config.DmlOptions.BatchUpdates.Enabled = true;
        //config.DmlOptions.BatchUpdates.AsynchronousBatch = true;
        //config.DmlOptions.InsertNullBehaviour = InsertNullBehaviour.Omit;
        private void InitOracle()
        {
#pragma warning disable 612, 618
            Assembly providerAssembly = Assembly.LoadWithPartialName("Devart.Data.Oracle.Entity");
#pragma warning restore 612, 618

            Type oracleEntityProviderConfigType = providerAssembly.GetType("Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig");
            Type oracleEntityProviderDmlConfigType = providerAssembly.GetType("Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderDmlConfig");
            Type oracleEntityProviderBatchUpdateConfigType = providerAssembly.GetType("Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderBatchUpdateConfig");
            Type insertNullBehaviourType = providerAssembly.GetType("Devart.Data.Oracle.Entity.Configuration.InsertNullBehaviour");

            PropertyInfo oracleEntityProviderConfigProperty = oracleEntityProviderConfigType.GetProperty("Instance", BindingFlags.Static | BindingFlags.Public);

            PropertyInfo queryOptionsProperty = oracleEntityProviderConfigProperty.PropertyType.GetProperty("QueryOptions");
            PropertyInfo noEscapeLikeProperty = queryOptionsProperty.PropertyType.GetProperty("NoEscapeLike");
            PropertyInfo caseInsensitiveComparisonProperty = queryOptionsProperty.PropertyType.GetProperty("CaseInsensitiveComparison");
            PropertyInfo caseInsensitiveLikeProperty = queryOptionsProperty.PropertyType.GetProperty("CaseInsensitiveLike");

            PropertyInfo dmlOptionsProperty = oracleEntityProviderConfigProperty.PropertyType.GetProperty("DmlOptions");
            PropertyInfo batchUpdatesProperty = dmlOptionsProperty.PropertyType.GetProperty("BatchUpdates");
            PropertyInfo enabledProperty = batchUpdatesProperty.PropertyType.GetProperty("Enabled");
            PropertyInfo asynchronousBatchProperty = batchUpdatesProperty.PropertyType.GetProperty("AsynchronousBatch");
            PropertyInfo insertNullBehaviourProperty = dmlOptionsProperty.PropertyType.GetProperty("InsertNullBehaviour");

            object oracleEntityProviderConfigObject = oracleEntityProviderConfigProperty.GetValue(null, null);
            object queryOptionsObject = queryOptionsProperty.GetValue(oracleEntityProviderConfigObject, null);
            object dmlOptionsObject = dmlOptionsProperty.GetValue(oracleEntityProviderConfigObject, null);
            object batchUpdatesObject = batchUpdatesProperty.GetValue(dmlOptionsObject, null);
            object omitInsertNullBehaviourEnum = Enum.ToObject(insertNullBehaviourType, 3);

            noEscapeLikeProperty.SetValue(queryOptionsObject, true, null);
            caseInsensitiveComparisonProperty.SetValue(queryOptionsObject, true, null);
            caseInsensitiveLikeProperty.SetValue(queryOptionsObject, true, null);
            enabledProperty.SetValue(batchUpdatesObject, true, null);
            asynchronousBatchProperty.SetValue(batchUpdatesObject, true, null);
            insertNullBehaviourProperty.SetValue(dmlOptionsObject, omitInsertNullBehaviourEnum, null);
        }

This works in our environment with the 7.8.301 build of dotConnect for Oracle. Looking forward to your small test project with the corresponding DDL/DML script for reproducing. You can send it to us via our contact form: http://www.devart.com/company/contactform.html.

[GrMikeD]

Hi! I finally managed to isolate the error that was being thrown silently due to an empty catch statement.

By the time that the line: "oracleEntityProviderConfigProperty.GetValue(null, null)" is processed the following exception is thrown because it takes for granted that EF v6 should be loaded!

Code: Select all

'Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig' threw an exception.
       Source=Devart.Data.Oracle.Entity
       TypeName=Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig
       StackTrace:
            at Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.get_Instance()
       InnerException: System.IO.FileNotFoundException
            HResult=-2147024894
            Message=Could not load file or assembly 'EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.
            Source=Devart.Data.Oracle.Entity
            FileName=EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
            FusionLog==== Pre-bind state information ===

[Shalex]

Could you please confirm that config.QueryOptions.CaseInsensitiveLike works in your environment?

[GrMikeD]

Hi, I added reference to assembly Devart.Data.Oracle.Entity and confirm that CaseInsensitive settings work as expected.

If I try to initialize property through reflection, it crashes as it requires EF v6 even when being run from a .NET 4.0 application

Code: Select all

 ...
 object oracleEntityProviderConfigObject = oracleEntityProviderConfigProperty.GetValue(null, null);

System.IO.FileNotFoundException: Could not load file or assembly 'EntityFramework, Version=6.0.0.0

[Shalex]

Code: Select all

Assembly providerAssembly = Assembly.LoadWithPartialName("Devart.Data.Oracle.Entity");

Please use Assembly.Load Method(String) method (instead of Assembly.LoadWithPartialName) and specify the full name of Devart.Data.Oracle.Entity with its version.

Be aware that there are four versions of Devart.Data.Oracle.Entity.dll depending on the target Entity Framework: C:\Program Files (x86)\Devart\dotConnect\Oracle\Entity\.

[GrMikeD]

Thanks, using Assembly.Load works like a charm