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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Ansgar
Posts: 10
Joined: Tue 05 Mar 2013 07:25

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

Post by Ansgar » Wed 23 Jul 2014 07:59

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

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

Post by Shalex » Thu 24 Jul 2014 13:23

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

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Thu 31 Jul 2014 15:00

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
Posts: 10
Joined: Tue 05 Mar 2013 07:25

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

Post by Ansgar » Fri 29 Aug 2014 06:52

Thank you very much.

Best regards, Ansgar

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

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

Post by GrMikeD » Thu 23 Oct 2014 21:38

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 :roll:

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Fri 24 Oct 2014 14:01

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

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

Post by GrMikeD » Fri 31 Oct 2014 12:25

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
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

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

Post by MariiaI » Tue 04 Nov 2014 11:18

GrMikeD wrote: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.

Post Reply