FirstWithoutOrderByAndFilterWarning is not logged for First/FirstOrDefault on a collection navigation in a subquery

[SimonCropp]

FirstWithoutOrderByAndFilterWarning is not logged when First/FirstOrDefault is applied to a collection navigation inside a projection or a predicate. The subquery gets an unordered TOP(1) (SQL Server) or LIMIT 1 (SQLite), so which row it returns is arbitrary, which is the case the warning exists for.

Take/Skip in the same position do log RowLimitingOperationWithoutOrderByWarning, and First/FirstOrDefault at the root of a query does log FirstWithoutOrderByAndFilterWarning.

Example

With both warnings configured to throw:

context.Companies
    .OrderBy(_ => _.Id)
    .Select(_ => new
    {
        _.Name,
        First = _.Employees.FirstOrDefault()!.Name
    })
    .ToQueryString();

does not throw, and produces:

SELECT [c].[Name], (
    SELECT TOP(1) [e].[Name]
    FROM [Employees] AS [e]
    WHERE [c].[Id] = [e].[CompanyId]) AS [First]
FROM [Companies] AS [c]
ORDER BY [c].[Id]

The same for _.Employees.Select(_ => _.Name).FirstOrDefault(), and for a predicate, Where(_ => _.Employees.FirstOrDefault()!.Age > 30).

Query	Warning logged
FirstOrDefault() on a navigation in a projection	none
Select(...).FirstOrDefault() on a navigation in a projection	none
FirstOrDefault() on a navigation in a Where	none
Take(1) on a navigation in a projection	RowLimitingOperationWithoutOrderByWarning
FirstOrDefault() at the root	FirstWithoutOrderByAndFilterWarning

Same results with SQL Server and SQLite.

Expected

FirstWithoutOrderByAndFilterWarning is logged for First/FirstOrDefault on a navigation with no ordering and no filter, the same as at the root.

Cause

RelationalQueryableMethodTranslatingExpressionVisitor.TranslateFirstOrDefault only logs the warning when there is no predicate and no ordering:

if (selectExpression.Predicate == null
    && selectExpression.Orderings.Count == 0)
{
    _queryCompilationContext.Logger.FirstWithoutOrderByAndFilterWarning();
}

Navigation expansion turns c.Employees into a query filtered by the correlation c.Id == e.CompanyId, which becomes the subquery's Predicate, so it is treated like a user filter. But it does not narrow the result: each company can have many employees, so TOP(1) without an ordering still picks an arbitrary one. TranslateTake and TranslateSkip only check IsOrdered, which is why they do warn.

Repro

A console app is attached: EfCoreFirstWarningRepro.zip. It needs no database, since each query is only translated. dotnet run prints the SQL for each case, and exits with 1 when the issue reproduces.

Program.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

var failed = false;
foreach (var provider in new[] { "SqlServer", "Sqlite" })
{
    Console.WriteLine($"########## {provider}");
    using var context = new AppContext(provider);

    // expected to throw FirstWithoutOrderByAndFilterWarning, but does not
    failed |= !Warns(
        "FirstOrDefault on a navigation in a projection",
        () => context.Companies
            .OrderBy(_ => _.Id)
            .Select(_ => new
            {
                _.Name,
                First = _.Employees.FirstOrDefault()!.Name
            }));
    failed |= !Warns(
        "Select then FirstOrDefault on a navigation in a projection",
        () => context.Companies
            .OrderBy(_ => _.Id)
            .Select(_ => new
            {
                _.Name,
                First = _.Employees.Select(_ => _.Name).FirstOrDefault()
            }));
    failed |= !Warns(
        "FirstOrDefault on a navigation in a Where",
        () => context.Companies
            .OrderBy(_ => _.Id)
            .Where(_ => _.Employees.FirstOrDefault()!.Age > 30));

    // for comparison, these throw as expected
    Warns(
        "Take(1) on a navigation in a projection",
        () => context.Companies
            .OrderBy(_ => _.Id)
            .Select(_ => new
            {
                _.Name,
                First = _.Employees.Take(1).Select(_ => _.Name).ToList()
            }));
    // the warning is thrown while the query is compiled, before connecting
    Warns(
        "FirstOrDefault at the root",
        () => context.Employees
            .Select(_ => _.Name)
            .FirstOrDefault());
}

Console.WriteLine();
if (failed)
{
    Console.WriteLine("Reproduced: FirstWithoutOrderByAndFilterWarning was not logged for at least one subquery.");
    return 1;
}

Console.WriteLine("Not reproduced: every subquery logged FirstWithoutOrderByAndFilterWarning.");
return 0;

static bool Warns(string name, Func<object?> query)
{
    Console.WriteLine();
    Console.WriteLine($"=== {name}");
    try
    {
        var sql = ((IQueryable) query()!).ToQueryString();
        Console.WriteLine("No warning. SQL:");
        Console.WriteLine(sql);
        return false;
    }
    catch (InvalidOperationException exception) when (exception.Message.Contains("An error was generated for warning"))
    {
        var start = exception.Message.IndexOf('\'') + 1;
        var end = exception.Message.IndexOf('\'', start);
        Console.WriteLine($"Warning thrown: {exception.Message[start..end]}");
        return true;
    }
}

public class Company
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public List<Employee> Employees { get; set; } = [];
}

public class Employee
{
    public int Id { get; set; }
    public int CompanyId { get; set; }
    public string Name { get; set; } = "";
    public int Age { get; set; }
}

public class AppContext(string provider) :
    DbContext
{
    public DbSet<Company> Companies => Set<Company>();
    public DbSet<Employee> Employees => Set<Employee>();

    protected override void OnConfiguring(DbContextOptionsBuilder builder)
    {
        if (provider == "SqlServer")
        {
            builder.UseSqlServer("Server=.;Database=Repro;Trusted_Connection=True");
        }
        else
        {
            builder.UseSqlite("Data Source=repro.db");
        }

        builder.ConfigureWarnings(_ => _.Throw(
            CoreEventId.FirstWithoutOrderByAndFilterWarning,
            CoreEventId.RowLimitingOperationWithoutOrderByWarning));
    }
}

Versions

• EF Core 10.0.12
• Microsoft.EntityFrameworkCore.SqlServer 10.0.12
• Microsoft.EntityFrameworkCore.Sqlite 10.0.12
• .NET 10

[SimonCropp]

EfCoreFirstWarningRepro.zip

[SimonCropp]

let me know if u want me to attempt a PR to fix it