String.empty problem - please help!

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
cew3
Posts: 20
Joined: Wed 15 Jul 2009 14:50

String.empty problem - please help!

Post by cew3 » Tue 08 Nov 2016 15:38

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: String.empty problem - please help!

Post by Pinturiccio » Wed 09 Nov 2016 11:48

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
Posts: 20
Joined: Wed 15 Jul 2009 14:50

Re: String.empty problem - please help!

Post by cew3 » Wed 09 Nov 2016 12:17

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: String.empty problem - please help!

Post by Pinturiccio » Thu 10 Nov 2016 12:07

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

cew3
Posts: 20
Joined: Wed 15 Jul 2009 14:50

Re: String.empty problem - please help!

Post by cew3 » Mon 14 Nov 2016 06:16

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

Re: String.empty problem - please help!

Post by Shalex » Tue 15 Nov 2016 12:47

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
Posts: 20
Joined: Wed 15 Jul 2009 14:50

Re: String.empty problem - please help!

Post by cew3 » Thu 01 Dec 2016 12:27

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

Re: String.empty problem - please help!

Post by Shalex » Fri 02 Dec 2016 20:05

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
Posts: 20
Joined: Wed 15 Jul 2009 14:50

Re: String.empty problem - please help!

Post by cew3 » Tue 13 Dec 2016 09:31

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

Re: String.empty problem - please help!

Post by Shalex » Fri 16 Dec 2016 11:23

cew3 wrote: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.
cew3 wrote: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.
cew3 wrote: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
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: String.empty problem - please help!

Post by Shalex » Mon 19 Dec 2016 20:14

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

Re: String.empty problem - please help!

Post by Shalex » Mon 16 Jan 2017 16:39

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
Posts: 20
Joined: Wed 15 Jul 2009 14:50

Re: String.empty problem - please help!

Post by cew3 » Tue 17 Jan 2017 10:05

It works, thank you!

Best regards,
cew3

Post Reply