Generated sql for Linq Any method

[Remco Blok]

Hello,

I'm curious why when you use the Linq Any method the generated sql looks something like:

CASE WHEN EXISTS (a) THEN 1 WHEN NOT EXISTS (a) THEN 0 END

where a is the same stament executed both by WHEN EXISTS and WHEN NOT EXISTS. I'm not sure how the Oracle optimiser deals with this, but I would have thought it would be better to generate the following sql instead:

CASE WHEN EXISTS (a) THEN 1 ELSE 0 END

Why is the former sql generated and not the latter?

Remco

[Shalex]

Thank you for your report. We have reproduced the issue. We will investigate it and notify you about the results.

[Shalex]

SQL generation for .Any method in LINQ to Entities is improved. We will post here when the corresponding build of dotConnect for Oracle is available for download.

[Shalex]

New version of dotConnect for Oracle 6.80 is released!
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).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=23639 .

[Remco Blok]

Hi Devart,

Thans for improving the sql for the Any method. I came accross another scenario that produces similar sql. Say you have the following linq statement:

from user in this.Context.Users
select user.SomeProperty == "Y"

That would result in sql like:

SELECT
CASE WHEN "Extent1".SOME_PROPERTY = 'Y' THEN 1 WHEN "Extent1".SOME_PROPERTY 'Y' THEN 0 END AS C1
FROM USERS "Extent1"

I suppose this could be better written as:

SELECT
CASE WHEN "Extent1".SOME_PROPERTY = 'Y' THEN 1 ELSE 0 END AS C1
FROM USERS "Extent1"

I also came accross the following strange scenario. This time I query a navigation property of a 1 to 0 or 1 relationship:

from user in this.Context.Users
select user.Action == null

This produces the following sql:

SELECT
CASE WHEN "Extent3".USER_ID IS NULL THEN 1 ELSE 0 END AS C1
FROM USERS "Extent1"
LEFT OUTER JOIN USER_ACTION "Extent2" ON "Extent1".USER_ID = "Extent2".USER_ID
LEFT OUTER JOIN USER_ACTION "Extent3" ON "Extent2".USER_ID = "Extent3".USER_ID

Why does it generate the same left outer join twice? The sql should really have been:

SELECT
CASE WHEN "Extent2".USER_ID IS NULL THEN 1 ELSE 0 END AS C1
FROM USERS "Extent1"
LEFT OUTER JOIN USER_ACTION "Extent2" ON "Extent1".USER_ID = "Extent2".USER_ID

If I do the opposite

from user in this.Context.Users
select user.Action != null

then it gets even weirder:

SELECT
CASE WHEN "Extent3".USER_ID IS NOT NULL THEN 1 WHEN "Extent4".USER_ID IS NULL THEN 0 END AS C1
FROM USERS "Extent1"
LEFT OUTER JOIN USER_ACTION "Extent2" ON "Extent1".USER_ID = "Extent2".USER_ID
LEFT OUTER JOIN USER_ACTION "Extent3" ON "Extent2".USER_ID = "Extent3".USER_ID
LEFT OUTER JOIN USER_ACTION "Extent4" ON "Extent2".USER_ID = "Extent4".USER_ID

Now the same left outer join is generated three times!

If I do the following:

from user in this.Context.Users
let action = user.Action
select action != null

SELECT
CASE WHEN "Extent3".USER_ID IS NOT NULL THEN 1 WHEN "Extent3".USER_ID IS NULL THEN 0 END AS C1
FROM USERS "Extent1"
LEFT OUTER JOIN USER_ACTION "Extent2" ON "Extent1".USER_ID = "Extent2".USER_ID
LEFT OUTER JOIN USER_ACTION "Extent3" ON "Extent2".USER_ID = "Extent3".USER_ID

Now we're down to two left outer joins again. The sql should really have been:

SELECT
CASE WHEN "Extent2".USER_ID IS NOT NULL THEN 1 ELSE 0 END AS C1
FROM USERS "Extent1"
LEFT OUTER JOIN USER_ACTION "Extent2" ON "Extent1".USER_ID = "Extent2".USER_ID

Could Devart investigate this please?

many thanks

Remco

[Shalex]

SELECT
CASE WHEN "Extent1".SOME_PROPERTY = 'Y' THEN 1 WHEN "Extent1".SOME_PROPERTY 'Y' THEN 0 END AS C1
FROM USERS "Extent1"

I also came accross the following strange scenario. This time I query a navigation property of a 1 to 0 or 1 relationship: ...

Thank you for your report. We have reproduced these both issues. We will investigate them and notify you about the results as soon as possible.

[Shalex]

CASE generation is improved.
We will post here when the corresponding build of dotConnect for Oracle is available for download.

[Shalex]

New version of dotConnect for Oracle 7.1 is released!
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).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=24522 .

[Remco Blok]

Hi Shalex,

I had not responded to this earlier to thank you for the fix, because I thought it was only partially fixed. I saw the CASE generation was improved but there were still duplicated left outer joins. However, I noticed that with .NET 4.5 the duplicated left outer join issue is resolved as well. So that issue must have been in the core Entity Framework code in the .NET Framework (and I mean the core EF code, not the EF NuGet package), rather than in the Devart provider.

many thanks

Remco