CROSS/OUTER APPLY - Still a problem in RTM?

All replies

  • A related question that might help us solve this:

    Is it possible to inspect the query that EF sends to the dataprovider so we can try to determine how to get around it?
    Thursday, December 11, 2008 3:00 PM
  • And yet another question:

    Is it possible to manipulate the expression tree so we can replace OUTER APPLY with something that works with something else than T-SQL?
    Friday, December 12, 2008 9:44 AM
  • Any news on this topic?

    By the way, the OUTER APPLY and CROSS APPLY statements are not supported by MySQL and PostgreSQL servers.
    Saturday, December 27, 2008 7:49 AM
  • .. or Oracle. AFAIK, only MSSQL 2005 and newer supports this. This is why it is a huge problem if EF has no way of avoiding this syntax.
    Sunday, December 28, 2008 11:36 PM
  • Hello,

    I have forwarded your question to some members of the team that may have more detail on the current state of this issue. We have worked and will continue working to avoid emitting queries containing CROSS APPLY and OUTER APPLY operators if it is possible to replace them with a construct that is more widely supported, but there are still scenarios in which we still don't have better alternatives. Those cases are mentioned in the following page:

    Known Issues in the .NET Framework Data Provider for SQL Server (SqlClient) for the Entity Framework

    The following are some typical scenarios that might lead to the presence of CROSS APPLY and/or OUTER APPLY operators in the output query:

    • A correlated subquery with paging.

    • An AnyElement over a correlated sub-query, or over a collection produced by navigation.

    • LINQ queries that use grouping methods that accept an element selector.

    • A query in which a CROSS APPLY or an OUTER APPLY is explicitly specified

    • A query that has a DEREF construct over a REF construct. 

    (I copy an excerpt of the documentation here with the hope that this will help you avoid such scenarios)

    Regarding your questions on how to inspect or manipulate query trees: One path in that direction with EF is to write yourself an ADO.NET Provider. Entity Framework produces a query tree that the ADO.NET provider has to translate to the native query language of the target database product. It is possible to write a "wrapping" ADO.NET Provider that allows for inspecting the query trees before delegating execution to a full fledged provider. However, query trees are not publicly constructible in v1 and therefore it is no possible to replace a query tree passed to the underlying provider with one that has been manipulated.

    Hope this helps,

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, December 29, 2008 1:31 AM
  • Diego,

     Is there a place I could send information about a situation that I don't think meets the criteria you outlined above that EF seems to be generating an Outer Apply?  Should I just post it here to the forum, Connect, email to someone?  I can't see what EF is translating the expression to, but I know that my Oracle provider (DevArt) is seeing it as an Outer Apply and failing (because Oracle doesn't support Outer Apply), but the same scenario on SQL Server is working correctly and the generated SQL doesn't not use an Outer Apply.  I have sent the issue to DevArt as well in case it is a bug on their side, but if EF is truly generating an Outer Apply in this case it would seem strange since the SQL Server provider isn't translating it that way. 



    Saturday, January 24, 2009 1:18 AM
  • Bryan,

    Feel free to email me (diego dot vega at microsoft dot com). I will foward it to the appropriate people.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Saturday, January 24, 2009 3:56 AM
  • Diego,

    I'm currently working on a project involving an Oracle Database and Entity Framework. 

    I've ran into this issue as well by using the .Include method on one of my entities (for a multiple criterion search query). 

    Has there been any improvement on that topic ? Is there any workaround ?


    Tuesday, December 08, 2009 2:04 PM
  • Still the same problem here with Sqlite.

    Generated by includes
    Tuesday, August 24, 2010 11:45 AM
  • Is there no interesst in this problem from entity framework team????
    In this major problem, every ADO.Net provider except the MS SQL ones has.
    (every non MS i know, for example MySql, Oracle, SQLite, Posgres )

    Can please somebody give a response why this problem (since December 11, 2008) is still there?
    And are you planing to make a option here or somethink?
    Monday, August 30, 2010 10:45 AM
  • Thank you for your question. We understand that APPLY is not supported by many providers and therefore we did put a significant effort even before shipping the .NET 3.5 version of the Entity Framework to minimize the cases in which the output command tree contains an APPLY.  That said, we are certainly interested in investigating and evaluating cases in which we still produce an APPLY that can be avoided.  Could you please provide your repro where the Include causes the output query to contain an APPPY?  

    Kati Iceva

    Entity Framework Developer

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, September 23, 2010 6:05 AM
  • Hi Kati,

    here is one sample that shows one condition very clear.

    I think Lingzhi Sun have a demo for this problem. So you can easly reproduce this apply condition.

    Best regards


    Monday, September 27, 2010 3:28 PM
  • it would be great to have a VERY clear answer to what to do with the "outer/cross apply" thing. If im not using SQL Server, how can i solve the problem?

    since its only SQL server 2005 & 2008 that support the "outer/cross apply", we should have a way to tell EF to not produce theses commands. Every providers that i saw (devart for oracle, mysql, etc..) havent found a way to solve this yet! So i think the EF team should give us a way to turn this feature OFF. Its not normal that this thing is not solved yet!


    thank you!


    Monday, November 29, 2010 9:22 PM
  • Still nothink?

    Come on you must be joking EF Team!? This problem must be solved!
    Pls give us some answere if / what you do for this really major problem!

    Its only to fix your SQL generator library.
    Friday, December 10, 2010 3:59 PM
  • *knock knock* someone out there?

    Is this nasty problem fixed in EF5 CTP5????

    I tested it know with the CTP5, still the same -_-°

    Wednesday, January 19, 2011 3:43 PM
  • Hi Steffen,

    First, I apologize for not getting back to you sooner. We hear your concerns, we understand that APPLY is not supported by many providers and we continue to work on minimizing the cases in which the output command tree contains an APPLY. 

    However, given that we use APPLY internally to reason about certain constructs in queries, and only later we try to safely transform it into other operations, likely there still would be cases that would not be covered in the next release.

    Also, I have been able to repro the case you pointed us to, and have added it to a list of things we would like to address.  Thank you for bringing that scenario to our attention.


    Kati Iceva



    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, January 21, 2011 8:38 PM
  • Hi Kati,

    nice to hear from you again.

    Ok, thats let me hope to watch forward to a new version.
    Do you have some kind of roadmap? Is there a timespan for a new version or must i wait until the next Framework version?


    Steffen Mangold

    Friday, February 11, 2011 8:35 AM
  • Anyone test this in EF 4.1? Is it now working?
    Wednesday, October 05, 2011 3:58 PM

    Any Update on this Issue? Iam having this OUTER APPLY issue with EF4 / Oracle 11g.





    Thursday, October 13, 2011 9:34 PM
  • Hi, I recently had the problem with outer apply and the oracle here is my example: 

    create table ParameterSets
    Id number(10, 0) not null,
    Name nvarchar2(50) not null,
    constraint ParameterSet_PK primary key (Id)
    create table Parameters
    Id number(10, 0),
    Name nvarchar2(50),
    ParameterSetId number(10, 0) not null,
    constraint Parameters_PK primary key (Id),
    constraint Parameters_ParamSet_FK foreign key (ParameterSetId) references ParameterSets(Id)
    create table UserGroups
    Id number(10, 0) not null, 
    Name nvarchar2(50) not null, 
    ParameterSetId number(10, 0) not null,
    constraint UserGroups_PK primary key (Id),
    constraint UserGroups_ParamSet_FK foreign key (ParameterSetId) references ParameterSets(Id)
    create table Users
    Id number(10,0) not null,
    GroupId number(10,0) not null,
    Name nvarchar2(50) not null,
    constraint Users_PK primary key (Id),
    constraint Users_Groups_FK foreign key (GroupId) references UserGroups(Id)

    The query: 

    var query = from u in ctx.Users.Include("UserGroup.ParameterSet.Parameters") 
    where u.Id == 1 
    select u;

    Generated sql: 

    SELECT "Project1"."ID2" AS "ID", "Project1"."ID" AS "ID1",
           "Project1"."GROUPID" AS "GROUPID", "Project1"."NAME" AS "NAME",
           "Project1"."ID1" AS "ID2", "Project1"."NAME1" AS "NAME1",
           "Project1"."ID3" AS "ID3", "Project1"."NAME2" AS "NAME2",
           "Project1"."C1" AS "C1", "Project1"."ID4" AS "ID4",
           "Project1"."NAME3" AS "NAME3",
    FROM   (SELECT "Filter1"."ID1" AS "ID", "Filter1"."GROUPID" AS "GROUPID",
                    "Filter1"."NAME1" AS "NAME", "Filter1"."ID2" AS "ID1",
                    "Filter1"."NAME2" AS "NAME1",
                    "Filter1"."PARAMETERSETID1" AS "PARAMETERSETID",
                    "Filter1"."ID3" AS "ID2", "Filter1"."ID4" AS "ID3",
                    "Filter1"."NAME3" AS "NAME2", "Filter2"."ID5" AS "ID4",
                    "Filter2"."NAME4" AS "NAME3",
                    "Filter2"."PARAMETERSETID2" AS "PARAMETERSETID1",
                       WHEN ("Filter2"."ID5" IS NULL) THEN
                     END AS "C1"
             FROM   (SELECT "Extent1"."ID" AS "ID1",
                             "Extent1"."GROUPID" AS "GROUPID",
                             "Extent1"."NAME" AS "NAME1", "Extent2"."ID" AS "ID2",
                             "Extent2"."NAME" AS "NAME2",
                             "Extent2"."PARAMETERSETID" AS "PARAMETERSETID1",
                             "Extent3"."ID" AS "ID3", "Extent3"."NAME" AS "NAME5",
                             "Extent3"."PARAMETERSETID" AS "PARAMETERSETID3",
                             "Extent4"."ID" AS "ID4", "Extent4"."NAME" AS "NAME3"
                      FROM   "TESTDB"."USERS" "Extent1"
                      INNER  JOIN "TESTDB"."USERGROUPS" "Extent2" ON "Extent1"."GROUPID" =
                      LEFT   OUTER JOIN "TESTDB"."USERGROUPS" "Extent3" ON "Extent1"."GROUPID" =
                      LEFT   OUTER JOIN "TESTDB"."PARAMETERSETS" "Extent4" ON "Extent3"."PARAMETERSETID" =
                      WHERE  (1 = "Extent1"."ID")) "Filter1"
             OUTER  APPLY (SELECT "Extent5"."ID" AS "ID5",
                                 "Extent5"."NAME" AS "NAME4",
                                 "Extent5"."PARAMETERSETID" AS "PARAMETERSETID2",
                                 "Extent6"."ID" AS "ID6",
                                 "Extent6"."NAME" AS "NAME6",
                                 "Extent6"."PARAMETERSETID" AS "PARAMETERSETID4"
                          FROM   "TESTDB"."PARAMETERS" "Extent5"
                          INNER  JOIN "TESTDB"."USERGROUPS" "Extent6" ON "Extent6"."PARAMETERSETID" =
                          WHERE  ("Filter1"."GROUPID" = "Extent6"."ID")) "Filter2") "Project1"
    ORDER  BY "Project1"."ID2" ASC, "Project1"."ID" ASC, "Project1"."ID1" ASC,
              "Project1"."ID3" ASC, "Project1"."C1" ASC

    • Edited by indomitable Friday, February 17, 2012 1:51 PM
    Friday, February 17, 2012 1:50 PM
  • Still no fix for this problem...

    I can't believe it!

    Monday, March 26, 2012 11:51 AM
  • Is anybody from MS able to give us feedback on this issue?

    When is a bugfix available?

    Do you need more test cases?

    I have asked at the Oracle ODP.Net Forum for a workaround. They told me that they don't have a chance to make workaround. Only MS can provide one...



    Wednesday, March 28, 2012 7:00 AM
  • Still nothink? Unbelievable...
    Thursday, August 23, 2012 9:59 AM
  • Still no fix for this problem...

    I can't believe it!

    Wednesday, May 29, 2013 2:16 PM
  • I too find it quite amazing that MS are just ignoring this major problem.

    I really think they should just be honest, and say one way or the other, whether it will be fixed, and give a timescale.

    If there is no intention to fix these issues, then they should just come out and admit that EF is only really designed to work with MS SQL Server.

    Very disappointing attitude overall.

    Wednesday, May 29, 2013 5:06 PM
  • A year since last post has passed. Any progress yet?
    I tested the latest Npgsql (postgresql driver) with EF 6.1 and it crashed because of APPLY.

    I tested with a very simple Include expression.

    Sunday, May 04, 2014 5:31 PM
  • I get the feeling that APPLY is not something easily fixed in EF6. We use Oracle at my work and we have to be careful because if it.

    I think(hope?) this is one of the issues EF7 will take care of. Reading between the lines about EF7, it sounds like the underlying providers will have much more control over the queries generated, which will hopefully allow them to handle the apply issues better.

    Saturday, March 21, 2015 3:27 PM
  • Andrew, 

    We use Oracle at work also and I'm running into the same issue.  How do you normally work around this?



    Tuesday, April 21, 2015 9:17 AM