Set operation over a projection containing a null-checked to-one non-entity subquery no longer translates (11 preview.7 regression)

[benedict-odonovan]

Bug description

The following bug isn't present in 11.0.0-preview.6.26359.118 but is in subsequent releases:
A projection that binds a to-one subquery over a non-entity type (.Select(x => new { ... }).FirstOrDefault()), references it more than once, and compares it to null as a whole object, can no longer take part in a set operation.

In Northwind terms — summarise each customer by their most recent order, and concatenate that with the customers who have never ordered:

// The customers who have ordered, summarised by their most recent order.
IQueryable<CustomerSummary> withOrders =
    from c in context.Customers
    let latest = c.Orders
        .OrderByDescending(o => o.OrderDate)
        .Select(o => new { o.OrderID, o.OrderDate })
        .FirstOrDefault()
    select new CustomerSummary
    {
        CustomerID = c.CustomerID,
        City = c.City,
        LatestOrderID = latest != null ? latest.OrderID : 0,
        LatestOrderDate = latest != null ? latest.OrderDate : null,
    };

// ... concatenated with the ones who never have.
IQueryable<CustomerSummary> neverOrdered = context.Customers
    .Where(c => !c.Orders.Any())
    .Select(c => new CustomerSummary
    {
        CustomerID = c.CustomerID,
        City = c.City,
        LatestOrderID = 0,
        LatestOrderDate = null,
    });

_ = withOrders.ToQueryString();                       // fine
_ = withOrders.Concat(neverOrdered).ToQueryString();  // throws

System.InvalidOperationException: Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call.

Only scalars (string, int, DateTime?) cross the set operation here — the anonymous type is consumed entirely inside the projection. The advice in the message isn't actionable either: the two sides are independently-built IQueryable<CustomerSummary>s combined by a shared helper, so there is no later Select to move the set operation ahead of.

On its own, withOrders now translates by lowering the subquery to a LEFT JOIN carrying a marker column:

SELECT [c].[CustomerID], [c].[City], [o1].[OrderID], [o1].[OrderDate], [o1].[marker]
FROM [Customers] AS [c]
LEFT JOIN (
    SELECT [o0].[OrderID], [o0].[OrderDate], [o0].[marker], [o0].[CustomerID]
    FROM (
        SELECT [o].[OrderID], [o].[OrderDate], 1 AS [marker], [o].[CustomerID], ROW_NUMBER() OVER(PARTITION BY [o].[CustomerID] ORDER BY [o].[OrderDate] DESC) AS [row]
        FROM [Orders] AS [o]
    ) AS [o0]
    WHERE [o0].[row] <= 1
) AS [o1] ON [c].[CustomerID] = [o1].[CustomerID]

That marker is carried as a client projection, and SelectExpression.ApplySetOperation rejects a set operation when either side has client projections — so the throw happens while translating the Concat itself, before anything downstream of it.

On 11.0.0-preview.6.26359.118 (and on 10.0.10) the same Concat translated to a single statement with no client projection:

SELECT [c].[CustomerID], [c].[City], CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Orders] AS [o]
        WHERE [c].[CustomerID] = [o].[CustomerID]) THEN (
        SELECT TOP(1) [o0].[OrderID]
        FROM [Orders] AS [o0]
        WHERE [c].[CustomerID] = [o0].[CustomerID]
        ORDER BY [o0].[OrderDate] DESC)
    ELSE 0
END AS [LatestOrderID], (
    SELECT TOP(1) [o1].[OrderDate]
    FROM [Orders] AS [o1]
    WHERE [c].[CustomerID] = [o1].[CustomerID]
    ORDER BY [o1].[OrderDate] DESC) AS [LatestOrderDate]
FROM [Customers] AS [c]
UNION ALL
SELECT [c0].[CustomerID], [c0].[City], 0 AS [LatestOrderID], NULL AS [LatestOrderDate]
FROM [Customers] AS [c0]
WHERE NOT EXISTS (
    SELECT 1
    FROM [Orders] AS [o2]
    WHERE [c0].[CustomerID] = [o2].[CustomerID])

What triggers it

All of these have to hold; drop any one and the query translates. Cases 4-6 in the repro below are the same query with exactly one condition removed:

1. the subquery is a to-one (FirstOrDefault/SingleOrDefault) over a non-entity type — an anonymous type or DTO;
2. the subquery result is referenced more than once in the projection;
3. at least one of those references is a whole-object != null / == null comparison;
4. the resulting query is then used in a set operation.

Your code

using Microsoft.EntityFrameworkCore;

using var context = new NorthwindContext();

Print("1. to-one subquery over an anonymous type, referenced twice, on its own",
    WithOrders());

Print("2. the same query, on the left of a Concat",
    WithOrders().Concat(NeverOrdered()));

Print("3. the same query, on the right of a Union",
    NeverOrdered().Union(WithOrders()));

Print("4. the same query with the subquery referenced only once, on the left of a Concat",
    WithOrdersSingleUse().Concat(NeverOrdered()));

Print("5. entity subquery instead of an anonymous type, referenced twice",
    EntitySubquery().Concat(NeverOrdered()));

Print("6. anonymous type referenced twice, but no whole-object null check",
    NoNullCheck().Concat(NeverOrdered()));

// `latest` is a to-one subquery projecting an anonymous type. It is referenced twice, and
// both references are whole-object null comparisons.
IQueryable<CustomerSummary> WithOrders()
    => from c in context.Customers
       let latest = c.Orders
           .OrderByDescending(o => o.OrderDate)
           .Select(o => new { o.OrderID, o.OrderDate })
           .FirstOrDefault()
       select new CustomerSummary
       {
           CustomerID = c.CustomerID,
           City = c.City,
           LatestOrderID = latest != null ? latest.OrderID : 0,
           LatestOrderDate = latest != null ? latest.OrderDate : null,
       };

// Identical apart from the second reference to `latest`.
IQueryable<CustomerSummary> WithOrdersSingleUse()
    => from c in context.Customers
       let latest = c.Orders
           .OrderByDescending(o => o.OrderDate)
           .Select(o => new { o.OrderID, o.OrderDate })
           .FirstOrDefault()
       select new CustomerSummary
       {
           CustomerID = c.CustomerID,
           City = c.City,
           LatestOrderID = latest != null ? latest.OrderID : 0,
           LatestOrderDate = null,
       };

// Identical apart from the subquery projecting the entity rather than an anonymous type.
IQueryable<CustomerSummary> EntitySubquery()
    => from c in context.Customers
       let latest = c.Orders.OrderByDescending(o => o.OrderDate).FirstOrDefault()
       select new CustomerSummary
       {
           CustomerID = c.CustomerID,
           City = c.City,
           LatestOrderID = latest != null ? latest.OrderID : 0,
           LatestOrderDate = latest != null ? latest.OrderDate : null,
       };

// Identical apart from the whole-object null comparisons.
IQueryable<CustomerSummary> NoNullCheck()
    => from c in context.Customers
       let latest = c.Orders
           .OrderByDescending(o => o.OrderDate)
           .Select(o => new { o.OrderID, o.OrderDate })
           .FirstOrDefault()
       select new CustomerSummary
       {
           CustomerID = c.CustomerID,
           City = c.City,
           LatestOrderID = latest.OrderID,
           LatestOrderDate = latest.OrderDate,
       };

IQueryable<CustomerSummary> NeverOrdered()
    => context.Customers
        .Where(c => !c.Orders.Any())
        .Select(c => new CustomerSummary
        {
            CustomerID = c.CustomerID,
            City = c.City,
            LatestOrderID = 0,
            LatestOrderDate = null,
        });

static void Print<T>(string title, IQueryable<T> query)
{
    Console.WriteLine($"===== {title} =====");
    try
    {
        Console.WriteLine(query.ToQueryString());
    }
    catch (Exception e)
    {
        Console.WriteLine($"{e.GetType().Name}: {e.Message}");
    }

    Console.WriteLine();
}

public class CustomerSummary
{
    public string CustomerID { get; set; } = null!;
    public string? City { get; set; }
    public int LatestOrderID { get; set; }
    public DateTime? LatestOrderDate { get; set; }
}

public class Customer
{
    public string CustomerID { get; set; } = null!;
    public string CompanyName { get; set; } = null!;
    public string? City { get; set; }
    public List<Order> Orders { get; set; } = null!;
}

public class Order
{
    public int OrderID { get; set; }
    public string? CustomerID { get; set; }
    public Customer? Customer { get; set; }
    public DateTime? OrderDate { get; set; }
}

public class NorthwindContext : DbContext
{
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlServer("Server=localhost;Database=Northwind;Trusted_Connection=True;TrustServerCertificate=True");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>().HasKey(c => c.CustomerID);
        modelBuilder.Entity<Customer>()
            .HasMany(c => c.Orders).WithOne(o => o.Customer).HasForeignKey(o => o.CustomerID);
    }
}

Stack traces

System.InvalidOperationException: Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call.
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplySetOperation(SetOperationType setOperationType, SelectExpression select2, Boolean distinct)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyUnion(SelectExpression source2, Boolean distinct)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateConcat(ShapedQueryExpression source1, ShapedQueryExpression source2)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorExpression[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass11_0`1.<ExecuteCore>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToQueryString(IQueryable source)

Verbose output

EF Core version

11.0.0-preview.7.26360.102

Database provider

No response

Target framework

.NET 11

Operating system

No response

IDE

No response

[benedict-odonovan]

A simple workaround for the time being: instead of null-checking the object, project a property that is required in the database as nullable, and null-check that instead.

IQueryable<CustomerSummary> withOrders =
    from c in context.Customers
    let latest = c.Orders
        .OrderByDescending(o => o.OrderDate)
        .Select(o => new { o.OrderID, o.OrderDate })
        .FirstOrDefault()
    select new CustomerSummary
    {
        CustomerID = c.CustomerID,
        City = c.City,
        LatestOrderID = latest != null ? latest.OrderID : 0,
        LatestOrderDate = latest != null ? latest.OrderDate : null,
    };

goes to

IQueryable<CustomerSummary> withOrders =
    from c in context.Customers
    let latest = c.Orders
        .OrderByDescending(o => o.OrderDate)
        .Select(o => new { OrderID = (int?)o.OrderID, o.OrderDate })
        .FirstOrDefault()
    select new CustomerSummary
    {
        CustomerID = c.CustomerID,
        City = c.City,
        LatestOrderID = latest.OrderID ?? 0,
        LatestOrderDate = latest.OrderID != null ? latest.OrderDate : null,
    };

[AndriySvyryd]

This was probably regressed by #38479