Page 1 of 1
Generated sql for Linq Any method
Posted: Tue 14 Feb 2012 11:23
by 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
Posted: Fri 17 Feb 2012 16:56
by Shalex
Thank you for your report. We have reproduced the issue. We will investigate it and notify you about the results.
Posted: Wed 29 Feb 2012 11:35
by 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.
Posted: Fri 16 Mar 2012 17:37
by 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 .
Posted: Mon 09 Apr 2012 08:45
by 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
Posted: Tue 10 Apr 2012 16:08
by Shalex
Remco Blok wrote:SELECT
CASE WHEN "Extent1".SOME_PROPERTY = 'Y' THEN 1 WHEN "Extent1".SOME_PROPERTY 'Y' THEN 0 END AS C1
FROM USERS "Extent1"
Remco Blok wrote: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.
Re: Generated sql for Linq Any method
Posted: Mon 02 Jul 2012 07:32
by Shalex
CASE generation is improved.
We will post here when the corresponding build of dotConnect for Oracle is available for download.
Re: Generated sql for Linq Any method
Posted: Thu 19 Jul 2012 10:22
by 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 .
Re: Generated sql for Linq Any method
Posted: Fri 24 Aug 2012 09:58
by 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