Page 1 of 1

"ORA-00936: missing expression" on 9.6.570

Posted: Mon 27 Aug 2018 06:46
by erolg
Hi,

the ef core linq expression

Code: Select all

dbContext.Class2.Where(two => 
                    two.Test && 
                    two.Threes.Any(three => three.Test && !three.IsDeleted ))
                .Select(o =>
                    new { 
                        o.OneId, 
                        NoFours = o.Threes.Any(three => 
                            three.Test &&
                            !three.IsDeleted &&
                            !three.Fours.Any(four =>
                                    !four.IsDeleted &&
                                    four.Test &&
                                    four.FiveId != null &&
                                    !four.Five.IsDeleted))})
                .ToList()
results to the generated sql

Code: Select all

SELECT "two"."OneId", CASE
    WHEN (
        SELECT CASE
            WHEN EXISTS (
                SELECT 1
                FROM "Class3" "three0"
                WHERE ((("three0"."Test" = 1) AND ("three0"."IsDeleted" = 0)) AND NOT EXISTS (
                    SELECT 1
                    FROM "Class4" "four"
                    LEFT JOIN "Class5" "four.Five" ON "four"."FiveId" = "four.Five"."Id"
                    WHERE (((("four"."IsDeleted" = 0) AND "four"."Test") AND "four"."FiveId" IS NOT NULL) AND ("four.Five"."IsDeleted" = 0)) AND "three0"."Id" = "four"."Class3Id")) AND "two"."Id" = "three0"."TwoId")
            THEN 1 ELSE 0
        END
        FROM DUAL
    ) = True
    THEN 1 ELSE 0
END AS "NoFours"
FROM "Class2" "two"
WHERE ("two"."Test" = 1) AND EXISTS (
    SELECT 1
    FROM "Class3" "three"
    WHERE (("three"."Test" = 1) AND ("three"."IsDeleted" = 0)) AND "two"."Id" = "three"."TwoId")
which throws the error "ORA-00936: missing expression". There are two errors in the statement.

First error in line 11:

WHERE (((("four"."IsDeleted" = 0) AND "four"."Test") AND "four"."FiveId" IS NOT NULL) AND ("four.Five"."IsDeleted" = 0)) AND "three0"."Id" = "four"."Class3Id")) AND "two"."Id" = "three0"."TwoId")

should be

WHERE (((("four"."IsDeleted" = 0) AND ("four"."Test" = 1)) AND "four"."FiveId" IS NOT NULL) AND ("four.Five"."IsDeleted" = 0)) AND "three0"."Id" = "four"."Class3Id")) AND "two"."Id" = "three0"."TwoId")


Second error in line 15:

FROM DUAL
) = True
THEN 1 ELSE 0

should be

FROM DUAL
) = 1
THEN 1 ELSE 0

Democode can be downloaded here

Re: "ORA-00936: missing expression" on 9.6.570

Posted: Tue 28 Aug 2018 17:03
by Shalex
Thank you for your report. We have reproduced the issue and will notify you when it is fixed.

Re: "ORA-00936: missing expression" on 9.6.570

Posted: Wed 07 Nov 2018 18:38
by Shalex
* The bug with throwing "ORA-00936: missing expression" in EF Core is fixed.
* The bug with using parentheses in binary expressions in EF Core is fixed.

We will notify you when the new public build of dotConnect for Oracle is available for download.

Re: "ORA-00936: missing expression" on 9.6.570

Posted: Wed 07 Nov 2018 18:42
by Shalex
Nuget package with the fix can be downloaded from https://www.devart.com/pub/nuget_oracle_9_6_630.zip.

Re: "ORA-00936: missing expression" on 9.6.570

Posted: Thu 29 Nov 2018 16:00
by Shalex
New build of dotConnect for Oracle 9.6.646 is available for download: viewtopic.php?f=1&t=38123.

Re: "ORA-00936: missing expression" on 9.6.570

Posted: Thu 19 Sep 2019 09:59
by Vidya
Still getting Missing Expression on 9.6.646 on linq queries

Re: "ORA-00936: missing expression" on 9.6.570

Posted: Fri 20 Sep 2019 18:01
by Shalex
Vidya wrote:
Thu 19 Sep 2019 09:59
Still getting Missing Expression on 9.6.646 on linq queries
Please upgrade to the newest (9.8.838 12-Sep-19) build of dotConnect for Oracle.

If the bug persists, send us a small test project for reproducing the issue.