String.empty problem - please help!

[cew3]

Hi there,

we currently use version 8.4.313 with .Net-Framework 4 and have a very strange problem that has to be solved:

Code: Select all

var query = db.MyTable
                   .Where(p => p.MyField != test)
                   .Select(p => p.ID);

results in a query like:

Code: Select all

SELECT 
"Extent1".ID
FROM "MyTable" "Extent1"
WHERE  NOT (((UPPER("Extent1"."MyField")) = (UPPER(:p__linq__0))) OR (("Extent1"."MyField" IS NULL) AND (:p__linq__0 IS NULL)))

This query works well but fails in case of

Code: Select all

var test = "";

In this case it allways returns null!

The query

Code: Select all

var query = db.MyTable
                   .Where(p => p.MyField != "")
                   .Select(p => p.ID);

results in

Code: Select all

SELECT 
"Extent1".ID
FROM "MyTable" "Extent1"
WHERE "Extent1"."MyField " IS NOT NULL

which returns the right result.

What has to be done to trigger the provider to create the right query in case of the parameters value is string.empty? (UseCSharpNullComparisonBehavior is alreday set to true)
I can't change ALL queries in our application and add an if (value == "") or something like that ...

Does the current version (9.x) of dotConnect for Oracle solve this problem?

Best regards!

[Pinturiccio]

Please create and send us a test project which reproduces the issue.

If your project have a size of several megabytes or more, you can archive your project and upload it to our ftp server ( ftp://ftp.devart.com/ , credentials: anonymous/anonymous ) or to any file exchange server so that we could download it from there. You can send us the password to the archive via our contact form.

Please also send us DDL/DML scripts reqiered for the project.

[cew3]

Hi Pinturiccio,
you can reproduce the problem by just using an existing database.

Add an additional column like this:

Code: Select all

"MyField" NVARCHAR2(400) DEFAULT '' 

Enter data to this column for some records.
Now try the linq query in you test application:

Code: Select all

var test = "";
var query = yourDB.YourTable
                   .Where(p => p.MyField != test)
                   .Select(p => p.ID);

You won't get the records having data in MyField.

[Pinturiccio]

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

[cew3]

Hi Pinturiccio,

how is it going? When do you think we will get a fix for this problem?
We had to stop delivering our application until this issue is resolved.

[Shalex]

The bug with comparison in LINQ query when config.QueryOptions.UseCSharpNullComparisonBehavior=true is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download. An approximate timeframe is 2 weeks.

[cew3]

Hi there,

the problem is still unsolved.
If the query contains two statesments within the where-clause, it does not work.

The following query:

Code: Select all

name = “foo“;
id = ““;

var query = from c in db.myTable
            where c.Name == name && c.ID != id
            select c.ID;

ends in

Code: Select all

SELECT 
"Extent1".ID
FROM "myTable" "Extent1"
WHERE ((((UPPER("Extent1"."Name")) = (UPPER(:p__linq__0))) AND ( NOT (("Extent1"."Name" IS NULL) OR (:p__linq__0 IS NULL)))) OR (("Extent1"."Name" IS NULL) AND (:p__linq__0 IS NULL))) 
AND ((UPPER("Extent1".ID)) <> (UPPER(:p__linq__1)))

But it should be:

Code: Select all

SELECT 
"Extent1".ID
FROM "myTable" "Extent1"
WHERE ((((UPPER("Extent1"."Name")) = (UPPER(:p__linq__0))) AND ( NOT (("Extent1"."Name" IS NULL) OR (:p__linq__0 IS NULL)))) OR (("Extent1"."Name" IS NULL) AND (:p__linq__0 IS NULL)))
AND NOT ((((UPPER("Extent1".ID)) = (UPPER(:p__linq__0))) AND ( NOT (("Extent1".ID IS NULL) OR (:p__linq__0 IS NULL)))) OR (("Extent1".ID IS NULL) AND (:p__linq__0 IS NULL)))

As you can see, the ID-field is not handled the right way in your generated query.

Please have a look at this as soon as possible.
Thanx!

[Shalex]

Your project uses Devart.Data.Oracle.Entity.EF4.dll, doesn't it? Please set contextInstance.ContextOptions.UseCSharpNullComparisonBehavior = true; before running your LINQ query.

Does this help? If not, make sure that the assemblies from the new (9.1.148) build are loaded in the process of your application (via the Debug > Windows > Modules window) and send us a small complete test project with the corresponding DDL/DML script for reproducing.

[cew3]

Hi,

I‘m sorry, but it is not possible to extract a „small“ sample out our application.
As you could have been able to reproduce the first reported bug with just 1 statement in the where-clause, you should be able to reproduce it with an additional string-column.

You wrote that you set the flag:
> contextInstance.ContextOptions.UseCSharpNullComparisonBehavior

Are you sure you got the right version to check?

I set the following flags:

Code: Select all

var queryOptions = OracleEntityProviderConfig.Instance.QueryOptions;
queryOptions.UseCSharpNullComparisonBehavior = true;
queryOptions.CaseInsensitiveLike = true;
queryOptions.CaseInsensitiveComparison = true;

OracleEntityProviderServices.HandleNullStringsAsEmptyStrings = true;

Loaded Modules:

Code: Select all

Devart.Data.dll (5.00.1572.0)
Devart.Data.Oracle.dll (9.01.148.0)
Devart.Data.Oracle.Entity.EF4.dll (9.01.148.0)

Best regards!

[Shalex]

You wrote that you set the flag:
> contextInstance.ContextOptions.UseCSharpNullComparisonBehavior

Are you sure you got the right version to check?

Thank you for the correction. The Devart option "queryOptions.UseCSharpNullComparisonBehavior = true;" should be used.

As you could have been able to reproduce the first reported bug with just 1 statement in the where-clause, you should be able to reproduce it with an additional string-column.

The only case when we can reproduce the problem with ignoring "queryOptions.UseCSharpNullComparisonBehavior = true;" is loading both Devart.Data.Oracle.Entity.EF4.dll and Devart.Data.Oracle.Entity.EF5.dll in the process of the same application (check via the Debug > Windows > Modules window). If so, there are two alternative ways to fix the issue:
1. Navigate to C:\Windows\Microsoft.NET\assembly\GAC_MSIL\ with some file manager (e.g.:https://x-diesel.com/) and remove the Devart.Data.Oracle.Entity.EF5 folder or move it to a different location.
2. Upgrade to v9.2.162 and select the 'Do not install assemblies in the GAC' check box, that disables placing the provider assemblies to the GAC, in the Install Wizard.

I‘m sorry, but it is not possible to extract a „small“ sample out our application.

If mixing Devart.Data.Oracle.Entity.EF4.dll and Devart.Data.Oracle.Entity.EF5.dll is not the case, send us a small complete test project with the corresponding DDL/DML script for reproducing. If you cannot reproduce the problem with a separate simple project, please complicate it step by step till "queryOptions.UseCSharpNullComparisonBehavior = true;" is ignored.

[Shalex]

Thank you for the additional information provided by email. The issue is ID related. We will investigate it and notify you about the result.

[Shalex]

The bug with comparing non-nullable properties in LINQ query when config.QueryOptions.UseCSharpNullComparisonBehavior=true is fixed in the newest (9.2.187) build of dotConnect for Oracle.

[cew3]

It works, thank you!

Best regards,
cew3