JOIN instead of CROSS APPLY in generated query in SQL Server

Open
#17,936 83 comments 48 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
25/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
csharp, sql
Domain
databases

Research direction

Use the supplied LINQ query as the reproducer and compare the Preview 5 CROSS APPLY SQL with the RC1 nested JOIN and ROW_NUMBER SQL. Done means the SQL Server provider generates the intended efficient query shape without the reported nested joins, while preserving the filtering, ordering, and Take(1) behavior.

Written by the indexing model from the issue text.

Description

area-perf area-query customer-reported

EF Core Preview 5 would generate CROSS APPLY from a linq query like this:

from navObject in Context.NavObjects
join vessel in Context.Vessels on navObject.VesselId equals vessel.VesselId
from passage in Context.Passages
    .Where(x => x.VesselId == navObject.VesselId && x.ActualDepartureTime.Value <= fromTime)
    .OrderByDescending(x => x.ActualDepartureTime)
    .Take(1)
    .DefaultIfEmpty()

The generated query would be:

SELECT ... FROM [NavObject] AS [no]
INNER JOIN [Vessel] AS [vessel] ON [no].[ObjectId] = [vessel].[ObjectId]
CROSS APPLY (
    SELECT TOP(1) [x].*
    FROM [Passage] AS [x]
    WHERE ([x].[ObjectId] = [no].[ObjectId]) AND ([x].[ActualDepartureTime] <= @__fromTime_1)
    ORDER BY [x].[ActualDepartureTime] DESC
) AS [t]

In RC1 the query contains JOINs from SELECTs from SELECTs which cause where bad performance and timeouts:

SELECT ... FROM [NavObject] AS [n]
INNER JOIN [Vessel] AS [v] ON [n].[ObjectId] = [v].[ObjectId]
INNER JOIN (
    SELECT [t].....
    FROM (
        SELECT [p]...., ROW_NUMBER() OVER(PARTITION BY [p].[ObjectId] ORDER BY [p].[ActualDepartureTime] DESC) AS [row]
        FROM [Passage] AS [p]
        WHERE ([p].[ActualDepartureTime] <= @__fromTime_1)
    ) AS [t]
    WHERE [t].[row] <= 1
) AS [t0] ON [n].[ObjectId] = [t0].[ObjectId]

As you can clearly see, the Preview 5 generated query is clear and effective while the RC1 generated query is off. Please fix this query generation pattern.

Further technical details

EF Core version: 3.0 RC1 (versus 3.0 Preview 5)
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.2.5

Dominant language
C#
Stars
14.8k
Forks
3.4k
Avg merge
2d 5h
Merged PRs (30d)
134

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from dotnet/efcore

All issues in dotnet/efcore

Similar issues

More C# issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.