dotConnect for Oracle CaseInsensitiveLike Problem

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
GrMikeD
Posts: 22
Joined: Tue 10 Nov 2009 20:24

dotConnect for Oracle CaseInsensitiveLike Problem

Post by GrMikeD » Fri 19 Jul 2013 12:11

Hi,

DotConnect for Oracle seems to ignore CaseInsensitiveLike setting since version 7.7x.
Last edited by GrMikeD on Tue 20 Aug 2013 10:11, edited 2 times in total.

Shalex
Site Admin
Posts: 8240
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for Oracle CaseInsensitiveLike Problem

Post by Shalex » Wed 24 Jul 2013 07:51

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

Shalex
Site Admin
Posts: 8240
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for Oracle CaseInsensitiveLike Problem

Post by Shalex » Tue 06 Aug 2013 15:05

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
Posts: 22
Joined: Tue 10 Nov 2009 20:24

Re: dotConnect for Oracle CaseInsensitiveLike Problem

Post by GrMikeD » Mon 12 Aug 2013 09:08

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
Site Admin
Posts: 8240
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for Oracle CaseInsensitiveLike Problem

Post by Shalex » Fri 16 Aug 2013 08:33

GrMikeD wrote:

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
Posts: 22
Joined: Tue 10 Nov 2009 20:24

Re: dotConnect for Oracle CaseInsensitiveLike Problem

Post by GrMikeD » Tue 20 Aug 2013 08:58

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
Site Admin
Posts: 8240
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for Oracle CaseInsensitiveLike Problem

Post by Shalex » Tue 20 Aug 2013 16:31

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

GrMikeD
Posts: 22
Joined: Tue 10 Nov 2009 20:24

Re: dotConnect for Oracle CaseInsensitiveLike Problem

Post by GrMikeD » Wed 21 Aug 2013 07:23

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
Site Admin
Posts: 8240
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for Oracle CaseInsensitiveLike Problem

Post by Shalex » Thu 22 Aug 2013 17:05

GrMikeD wrote:

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
Posts: 22
Joined: Tue 10 Nov 2009 20:24

Re: dotConnect for Oracle CaseInsensitiveLike Problem

Post by GrMikeD » Thu 29 Aug 2013 07:34

Thanks, using Assembly.Load works like a charm

Post Reply