Page 1 of 1
Oracle Numeric(1) and boolean values in EntityFramework/LINQ
Posted: Wed 23 Jul 2014 07:59
by 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
Re: Oracle Numeric(1) and boolean values in EntityFramework/LINQ
Posted: Thu 24 Jul 2014 13:23
by Shalex
Thank you for your report. We will investigate the question and notify you about the result.
Re: Oracle Numeric(1) and boolean values in EntityFramework/LINQ
Posted: Thu 31 Jul 2014 15:00
by 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.
Re: Oracle Numeric(1) and boolean values in EntityFramework/LINQ
Posted: Fri 29 Aug 2014 06:52
by Ansgar
Thank you very much.
Best regards, Ansgar
Re: Oracle Numeric(1) and boolean values in EntityFramework/LINQ
Posted: Thu 23 Oct 2014 21:38
by 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
Re: Oracle Numeric(1) and boolean values in EntityFramework/LINQ
Posted: Fri 24 Oct 2014 14:01
by 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.
Re: Oracle Numeric(1) and boolean values in EntityFramework/LINQ
Posted: Fri 31 Oct 2014 12:25
by 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!
Re: Oracle Numeric(1) and boolean values in EntityFramework/LINQ
Posted: Tue 04 Nov 2014 11:18
by MariiaI
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.