Oracle Numeric(1) and boolean values in EntityFramework/LINQ

[Ansgar]

Hello,

I've problem with a boolean value in a Oracle database, entity framework and dotconnect (Version 7.9.333) Here is a description:

The table: TEST
ID Numeric(11,0)
IsValid Numeric(1, 0)

In the field IsValid is saved 0 for false and -1 for true.

The following code works fine:

Code: Select all

For each item in ctx.TESTs
  if item.IsValid then 'IsValid return true and fals correctly
    'do something
  end if 
next 

The following code make problems:

Code: Select all

For each item in ctx.TESTs.where(function(p) p.isValid) 
  if item.IsValid then
    'do something
  end if 
next 

The collection is allways empty,because the generated SQL is

Code: Select all

SELECT 
"Extent1".ID,
"Extent1".ISVALID,
FROM TEST "Extent1" WHERE ("Extent1".ISVALID = 1) 

I need a generated SQL like
...where ISVALID <> 0

Does someone have a solution for the problem or have we to change the database values
from -1 to 1. That will bring huge effort for us, because we have a lot of old applications
with sql like value = -1.

Best regards, Ansgar

[Shalex]

Thank you for your report. We will investigate the question and notify you about the result.

[Shalex]

New build of dotConnect for Oracle 8.4.215 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).

The support of boolean properties, when the corresponding column is NUMBER(1, 0), is improved in this version (it generates where ISVALID <> 0).

For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=30078.

[Ansgar]

Thank you very much.

Best regards, Ansgar

[GrMikeD]

omg! since this version my LINQ "where IsActive=false" is converted to "IsActive<>1" and it no more uses my function-based index "... WHERE IsActive=0"

I just noticed the respective query causes delays at my productive system

I will have to change the index to "... WHERE IsActive<>1". Yet, I consider it as a "breaking" change

[Shalex]

By design, IsActive=false should be translated to "IsActive=0", and IsActive=true should be "IsActive<>0" in the generated SQL.
If you encounter a different behaviour, please send us a small test project for reproducing the issue.

[GrMikeD]

I think I found the problem

As you say, IsActive=false is translated to IsActive=0

BUT, !IsActive that, at a high programmatic level is equivalent to above, is translated to IsActive<>1

Since the domain of NUMBER(1) is [-9, 9], the first one uses an index on 0s of IsActive, the second one not!

[MariiaI]

I think I found the problem
As you say, IsActive=false is translated to IsActive=0
BUT, !IsActive that, at a high programmatic level is equivalent to above, is translated to IsActive<>1

Please specify the exact version of dotConnect for Oracle you are working with.
If it is not the latest one (8.4.274), please try upgrading your dotConnect for Oracle and try your scenario again. This build can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).

If this behaviour occurs with the latest build, too, please create a small test project and send it us, so that we are able to investigate it and find a solution for you in a shortest time.