Generated sql for Linq Any method

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Remco Blok
Posts: 25
Joined: Tue 14 Dec 2010 12:34

Generated sql for Linq Any method

Post by Remco Blok » Tue 14 Feb 2012 11:23

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 17 Feb 2012 16:56

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

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

Post by Shalex » Wed 29 Feb 2012 11:35

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 16 Mar 2012 17:37

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
Posts: 25
Joined: Tue 14 Dec 2010 12:34

Post by Remco Blok » Mon 09 Apr 2012 08:45

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 10 Apr 2012 16:08

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.

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

Re: Generated sql for Linq Any method

Post by Shalex » Mon 02 Jul 2012 07:32

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

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

Re: Generated sql for Linq Any method

Post by Shalex » Thu 19 Jul 2012 10:22

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
Posts: 25
Joined: Tue 14 Dec 2010 12:34

Re: Generated sql for Linq Any method

Post by Remco Blok » Fri 24 Aug 2012 09:58

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

Post Reply