Problem with bool WHERE clause in EFCore

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
seb1487
Posts: 23
Joined: Fri 24 Jun 2016 10:27

Problem with bool WHERE clause in EFCore

Post by seb1487 » Thu 09 Feb 2017 14:34

Hi Shalex,
dotConnect throws an error on an boolean Query like this (Oracle 10):

Code: Select all

_dbContext.Class1.FirstOrDefault(o => o.MyBoolProp)
Generated SQL:

Code: Select all

SELECT "o"."Id", "o"."MyBoolProp"
FROM "Class1" "o"
WHERE (ROWNUM <= 1) AND "o"."MyBoolProp"

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Problem with bool WHERE clause in EFCore

Post by Shalex » Fri 10 Feb 2017 19:49

Thank you for your report. We will notify you when the issue is fixed.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Problem with bool WHERE clause in EFCore

Post by Shalex » Mon 20 Feb 2017 19:46

The bug is fixed. We will notify you when the corresponding public build of dotConnect for Oracle is available for download.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Problem with bool WHERE clause in EFCore

Post by Shalex » Fri 03 Mar 2017 09:36

New build of dotConnect for Oracle 9.2.220 is available for download now: viewtopic.php?f=1&t=35058.

seb1487
Posts: 23
Joined: Fri 24 Jun 2016 10:27

Re: Problem with bool WHERE clause in EFCore

Post by seb1487 » Wed 08 Mar 2017 10:28

Hi Shalex,
It almost works, but with Take and subclasses there are still problems.

Code: Select all

 var works = dbContext.Class1
                .Include(o => o.OtherClass)
                .Where(o => o.IsDeleted == false) //Check is delete for Class1
                .OrderByDescending(o => o.Id)
                .Take(5)
                .ToList();

            var dontWorks = dbContext.Class1
                .Include(o => o.OtherClass)
                .Where(o => o.OtherClass.IsDeleted == false) //Check is delete for Class1.OtherClass
                .OrderByDescending(o => o.Id)
                .Take(5)
                .ToList();
Democode can be downloaded here.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Problem with bool WHERE clause in EFCore

Post by Shalex » Wed 15 Mar 2017 10:25

Thank you for the test project.

Both "works" and "dontWorks" return one record. The generated SQL:

Code: Select all

var works:
SELECT "t"."Id", "t"."IsDeleted", "t"."OtherClassId", "c"."Id", "c"."IsDeleted"
FROM (
    SELECT "o"."Id", "o"."IsDeleted", "o"."OtherClassId"
    FROM "Class1" "o"
    WHERE "o"."IsDeleted" = 0
    ORDER BY "o"."Id" DESC
) "t"
LEFT JOIN "Class2" "c" ON "t"."OtherClassId" = "c"."Id"
WHERE ROWNUM <= :p__p_0

Code: Select all

var dontWorks:
SELECT "t"."Id", "t"."IsDeleted", "t"."OtherClassId", "c"."Id", "c"."IsDeleted"
FROM (
    SELECT "o"."Id", "o"."IsDeleted", "o"."OtherClassId", "o.OtherClass"."Id" AS "c0", "o.OtherClass"."IsDeleted" AS "c1"
    FROM "Class1" "o"
    LEFT JOIN "Class2" "o.OtherClass" ON "o"."OtherClassId" = "o.OtherClass"."Id"
    WHERE "o.OtherClass"."IsDeleted" = 0
    ORDER BY "o"."Id" DESC, "o"."OtherClassId"
) "t"
LEFT JOIN "Class2" "c" ON "t"."OtherClassId" = "c"."Id"
WHERE ROWNUM <= :p__p_0
Could you please point to the problem?

seb1487
Posts: 23
Joined: Fri 24 Jun 2016 10:27

Re: Problem with bool WHERE clause in EFCore

Post by seb1487 » Tue 21 Mar 2017 10:04

Hi Shalex,
sorry, this was my mistake.
An additional bool property must be requested to reproduce this error.

Democode can be downloaded here.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Problem with bool WHERE clause in EFCore

Post by Shalex » Wed 22 Mar 2017 17:26

Thank you for the updated test project.

Are you getting the "An exception occured while reading a database value. The expected type was 'System.Int32' but the actual value was of type 'System.Boolean'." error when running the dontWorks query?

seb1487
Posts: 23
Joined: Fri 24 Jun 2016 10:27

Re: Problem with bool WHERE clause in EFCore

Post by seb1487 » Thu 23 Mar 2017 07:40

Yes this is the error message.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Problem with bool WHERE clause in EFCore

Post by Shalex » Thu 23 Mar 2017 08:34

We will notify you when the issue is fixed.

seb1487
Posts: 23
Joined: Fri 24 Jun 2016 10:27

Re: Problem with bool WHERE clause in EFCore

Post by seb1487 » Thu 06 Jul 2017 06:10

Hello Shalex,
there are already news about this bug?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Problem with bool WHERE clause in EFCore

Post by Shalex » Thu 06 Jul 2017 11:26

The investigation is in progress. As soon as we have any results, we will notify you.

Post Reply