EFCore 3.1 - How to generate SELECT query for database that does not support CROSS APPLY

[shvmgpt116]

I am working on developing EFCore provider for Oracle. Older Oracle databases (Ex:- 11.2) does not support CROSS APPLY join. In EFCore 2.2.4, there is a property called "IsLateralJoinSupported" in relational code (RelationalQueryCompilationContext.cs) , which can be overridden by database providers. Having the property return false allowed me to generate a SELECT query that does not have CROSS APPLY. This property seems to be missing in EFCore 3.1. I am not able to find any alternative which would generate query without using CROSS APPLY. Could anybody please provide the alternatives/workaround on how to generate the query in 3.1 which does not have CROSS APPLY.

Steps to reproduce

Further technical details

EF Core version: Microsoft.EntityFrameworkCore 3.1.3 Database provider: Target framework: .NET Core 3.0 Operating system: Windows 10 IDE: Visual Studio 2019 16.4.2

[smitpatel]

In 2.2.4 what IsLateralJoinedSupported did was to client eval when it was not supported. EF Core 3.x+ does not allow client eval of query which generates CROSS APPLY. So it is going to fail either way, hence we do not have such flag. Do you have alternate translation which you can do rather than CROSS APPLY? Sqlite does not support apply operations either and just throws invalid SQL.

[shvmgpt116]

I found an alternate to CROSS APPLY which does not require client evaluation-

If a query having CROSS APPLY is-

SELECT department_name, employee_id, employee_name
FROM   departments d
       CROSS APPLY (SELECT employee_id, employee_name
                    FROM   employees e
                    WHERE  salary >= 2000
                    AND    e.department_id = d.department_id)
ORDER BY 1, 2, 3;

An alternate to this would be-

SELECT department_name, employee_id, employee_name
FROM   departments d, employees e
WHERE  salary >= 2000
AND    e.department_id = d.department_id
ORDER BY 1, 2, 3;

Is it possible to re-introduce the property (IsLateralJoinSupported)? If it is set to false then can it generate a select expression that does not require to use cross apply?

[smitpatel]

We already do that conversion in EF Core.

[ajcvickers]

@shvmgpt116 Can you post the LINQ query that you are attempting to translate and the SQL that is currently generated?

[shvmgpt116]

@ajcvickers I am runnning one of the GIT tests available for EFCore 3.1 SimpleQueryOracleTest.SelectMany_correlated_with_outer_1

LINQ is-

ss => from c in ss.Set<Customer>()
      from o in ss.Set<Order>().Where(o => c.CustomerID == o.CustomerID).Select(o => c.City)
      select new { c, o }

Generated query in Oracle is-

SELECT "c"."CustomerID", "c"."Address", "c"."City", "c"."CompanyName",
"c"."ContactName", "c"."ContactTitle", "c"."Country", "c"."Fax", "c"."Phone",
"c"."PostalCode", "c"."Region", "t"."City" "o"
FROM "Customers" "c"
CROSS APPLY (
    SELECT "c"."City", "o"."OrderID", "o"."CustomerID"
    FROM "Orders" "o"
    WHERE ("c"."CustomerID" = "o"."CustomerID")
) "t"

[roji]

While playing around with the above, it seems we don't simplify even when projecting out to a dependent column, which seems a bit more useful than the above projection; we are generating a cross apply, although that can be simplified to a regular join.

from b in ctx.Blogs
from p in ctx.Posts.Where(p => p.BlogId == p.Id).Select(p => p.Title)
select new { b, p }

Full repro

```c# class Program { static void Main(string[] args) { using var ctx = new BlogContext(); ctx.Database.EnsureDeleted(); ctx.Database.EnsureCreated(); var results = ( from b in ctx.Blogs from p in ctx.Posts.Where(p => p.BlogId == p.Id).Select(p => p.Title) select new { b, p } ).ToList(); } } public class BlogContext : DbContext { public DbSet Blogs { get; set; } public DbSet Posts { get; set; } static ILoggerFactory ContextLoggerFactory => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information)); protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0") .UseLoggerFactory(ContextLoggerFactory); } public class Blog { public int Id { get; set; } public string Name { get; set; } public List Posts { get; set; } } public class Post { public int Id { get; set; } public int BlogId { get; set; } public string Title { get; set; } } ```

@shvmgpt116 the simplification from CROSS APPLY to join is an internal EF Core concern that shouldn't really affect providers - so I don't think there's a need to add IsLateralJoinedSupported again. Whenever we fix this on our side, your Oracle provider would automatically start generating the better code.

[shvmgpt116]

@roji Thanks for the clarification. Does this mean that changes will be made in the EF Core layer which will result in both the LINQ queries above not translating to CROSS APPLY? If yes, In which release can we expect this change to be merged in?

[roji]

@shvmgpt116 we'll discuss this in triage and assign a milestone appropriately.

Note that we already perform a lot of simplifications from CROSS APPLY to simple joins, for many useful scenarios, just not this one. It's also important to note that it's possible to get the desired SQL by rewriting your LINQ query with Include. This makes this issue lower-priority - we tend to prioritize issues where users really are blocked from producing the query they want, and that's not the case here.