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

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

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

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

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

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

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

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

Postby Ansgar » Fri 29 Aug 2014 06:52

Thank you very much.

Best regards, Ansgar
Ansgar
 
Posts: 10
Joined: Tue 05 Mar 2013 07:25

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

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

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

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

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

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

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

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


Return to dotConnect for Oracle