EF10 generated SQL query for ExecuteDeleteAsync when using Take not optimal

[matnun-br]

Bug description

As part of EF10 we can now use Take when using ExecuteDeleteAsync but the SQL generated is not optimal and does not perform as well as it could. The code provided generates the following SQL;

DELETE FROM [s]
FROM [Schema].[Entity] AS [s]
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT TOP(100) [s0].[Id]
        FROM  [Schema].[Entity] AS [s0]
        WHERE [s0].[field1] < 100
    ) AS [s1]
    WHERE [s1].[Id] = [s].[Id])

The sub query is not required. Instead the following optimal SQL should be generated;

DELETE tail
                FROM
                (
                    SELECT TOP (100) [Id]
                    FROM [Schema].[Entity]
                    WHERE [s0].[field1] < 100
                ) tail

Your code

await ctx.Set<Entity>().Where(e => e.field1 < 100).Take(100).ExecuteDeleteAsync();

Stack traces

Verbose output

EF Core version

10.0.8

Database provider

Microsoft.EntityFramework.SqlServer

Target framework

.Net 10.0

Operating system

Windows 11

IDE

VS Code 1.121.0

[AndriySvyryd]

I'm unable to repro this. Executing await ctx.Set<Entity>().Where(e => e.field1 < 100).Take(100).ExecuteDeleteAsync(); results in

DELETE TOP(100) FROM [s]
FROM [Schema].[Entity] AS [s]
WHERE [s].[field1] < 100

Please provide a small runnable project that shows the behavior that you see. If possible, use the latest 11 preview

[AndriySvyryd]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.