Query: Bulk Delete on result of First family in projection

[smitpatel]

    [ConditionalTheory]
    [MemberData(nameof(IsAsyncData))]
    public virtual Task Delete_GroupBy_Where_Select(bool async)
        => AssertDelete(
            async,
            ss => ss.Set<OrderDetail>()
                    .GroupBy(od => od.OrderID)
                    .Where(g => g.Count() > 5)
                    .Select(g => g.First()),
            rowsAffectedCount: 284);

This causes the shaper to be a projection which is entity. Even if we apply projection it causes a subquery in SelectExpression. We can come up with some SQL to represent this (though our current structure is not supported in delete operation and not easy way to convert to subquery)

[smitpatel]

The structure here is -> shaped query has projection binding which is another shaped query with entity projection. So ideally we can translate using subquery form.

[smitpatel]

Work-around

ss.Set<Animal>().Where(e => ss.Set<Animal>().GroupBy(e => e.CountryId)
                                                .Where(g => g.Count() < 3).Select(g => g.First()).Any(i => i == e).ExecuteDelete();

Using subquery form yourself. It should work for non-composite PK scenario.

[MichalLechowski]

I probably have a similar issue with bulk delete (EF Core 7, .NET 6).
The case is:

for every unique column pair Key + Name (none is PK column) remove all rows except the latest 10.
Ideal case for a bulk delete, database clean-up job.

So the query is something like:

var query = 
from uniquePairs in _dbContext.Logs.Select(o => new { o.Key, o.Name }).Distinct()
from log in _dbContext.Logs.Where(log => log.Key == uniquePairs .Key && log.Name == uniquePairs .Name)
                                                                   .OrderByDescending(log => log.Timestamp)
                                                                   .Skip(10)
select log;

When executing it with query.ExecuteDeleteAsync(), the result is:

System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. (Parameter 'index')
at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.GetProjection(ProjectionBindingExpression projectionBindingExpression)

when I try the other way:

var query = 
_dbContext.Logs
.GroupBy(x => new { x.Key, x.Name }, (k, g) => g.OrderByDescending(x => x.Timestamp).Skip(10))
.Select(x => x);

and then query.ExecuteDeleteAsync(), the result is:

.ExecuteDelete()' could not be translated. Additional information: The operation 'ExecuteDelete' requires an entity type which corresponds to the database table to be modified. The current operation is being applied on a non-entity projection. Remove any projection to non-entity types.

Obviously both of these queries work just fine with ToList and I get the exact collection of rows I wanna remove, the point is I don't wanna load them into memory at all.