String.empty problem - please help!

String.empty problem - please help!

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

Re: String.empty problem - please help!

Postby 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.
Pinturiccio
Devart Team
 
Posts: 1928
Joined: Wed 02 Nov 2011 09:44

Re: String.empty problem - please help!

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

Re: String.empty problem - please help!

Postby 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.
Pinturiccio
Devart Team
 
Posts: 1928
Joined: Wed 02 Nov 2011 09:44

Re: String.empty problem - please help!

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

Re: String.empty problem - please help!

Postby 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.
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: String.empty problem - please help!

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

Re: String.empty problem - please help!

Postby 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.
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: String.empty problem - please help!

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

Re: String.empty problem - please help!

Postby 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
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: String.empty problem - please help!

Postby 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
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: String.empty problem - please help!

Postby 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.
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: String.empty problem - please help!

Postby cew3 » Tue 17 Jan 2017 10:05

It works, thank you!

Best regards,
cew3
cew3
 
Posts: 20
Joined: Wed 15 Jul 2009 14:50


Return to dotConnect for Oracle