Support arbitrary expressions in inline collections (translate to VALUES)

[roji]

Our inline collection support currently supports constants:

public virtual Task Constant_Count_with_three_values(bool async)
    => AssertQuery(
        async,
        ss => ss.Set<PrimitiveCollectionsEntity>().Where(c => new[] { 2, 999, 1000 }.Count(i => i > c.Id) == 2),
        entryCount: 2);

While it's possible to also specify parameters, the NewArrayExpression gets client-evaluated by ParameterExtractingEV (because no database-correlated component), and so we get a single parameter for the array. This means we translate using OPENJSON, which isn't ideal - #30732 tracks doing a better translation to VALUES instead.

Beyond that, we could also allow arbitrary expressions:

var i = 2;

return AssertQuery(
    async,
    ss => ss.Set<PrimitiveCollectionsEntity>().Where(c => new[] { i, c.Int, SomeFunc(c.Foo) }.Contains(c.Id)),
    entryCount: 1);

The main blocker here is doing null semantics for InExpression over arbitrary expressions. Compensating to two-value logic isn't going to be easy...

[ajcvickers]

The query above still fails:

await context.Customers.Where(c => new[] { 2, 999, 1000 }.Count(i => i > c.Id) == 2).ToListAsync();

Unhandled exception. System.InvalidOperationException: The LINQ expression 'i => i > EntityShaperExpression: 
    Customer
    ValueBufferExpression:
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.Id' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitLambda[T](Expression`1 lambdaExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   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__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Program.<Main>$(String[] args) in C:\local\code\EF8SmallStuff\UseIn\Program.cs:line 11
   at Program.<Main>$(String[] args) in C:\local\code\EF8SmallStuff\UseIn\Program.cs:line 11
   at Program.<Main>(String[] args)

Full repro:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

await using var context = new SmallStuff();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

// var (i, j) = (2, 999);
// await context.Customers.Where(c => new[] { i, j }.Contains(c.Id)).ToListAsync();

await context.Customers.Where(c => new[] { 2, 999, 1000 }.Count(i => i > c.Id) == 2).ToListAsync();

public class SmallStuff : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=SmallStuff")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<OrderDetail> OrderDetails => Set<OrderDetail>();
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<OrderDetail>().HasKey(e => new { e.ProductId, e.OrderId });
    }
}

public class Customer
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public string? Region { get; set; }
    public List<Order> Orders { get; } = new();
}

public class Order
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public string? OrderRegion { get; set; }
    public Customer? Customer { get; set; }
    public List<OrderDetail> Details { get; } = new();
}

public class OrderDetail
{
    public int ProductId { get; set; }
    public int OrderId { get; set; }
    public int Count { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<OrderDetail> Details { get; } = new();
}

[roji]

Thanks, I'll take a look... There should be a test for the query in my OP, there must be some difference...

[ajcvickers]

@roji I searched for "Constant_Count_with_three_values", but it doesn't seem to exist.

[roji]

It was renamed to Inline_collection_Count_with_three_values, can be seen here.

[roji]

@ajcvickers your repro above works for me (using latest release/8.0), generating the following SQL:

SELECT [c].[Id], [c].[Name], [c].[Region]
FROM [Customers] AS [c]
WHERE (
    SELECT COUNT(*)
    FROM (VALUES (CAST(2 AS int)), (999), (1000)) AS [v]([Value])
    WHERE [v].[Value] > [c].[Id]) = 2

Can you take a look? There's also the test Inline_collection_Count_with_three_values which should cover this scenario.

[roji]

@ajcvickers can you take a look at this?

[ajcvickers]

@roji Yeah, I did look at this again and it was working for me, but I forgot to close it when I moved it back to the milestone.