Primitive collections cannot be projected when using server evaluation

[mu88]

Report

When trying to project a primitive collection, it fails when using server evaluation - only client evaluation works.

Include your code

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

const string buildingName = "City hall";

var connection = new SqliteConnection("Filename=:memory:");
await connection.OpenAsync();

var contextOptions = new DbContextOptionsBuilder<CustomContext>()
    .UseSqlite(connection)
    .Options;

await using var context1 = new CustomContext(contextOptions);
await context1.Database.EnsureDeletedAsync();
await context1.Database.EnsureCreatedAsync();
context1.Buildings.AddRange(
    new Building { Id = "1", Name = buildingName, HouseKeys = ["HK1", "HK2"] },
    new Building { Id = "2", Name = buildingName, HouseKeys = ["HK3", "HK4"] });
await context1.SaveChangesAsync();

await using var context2 = new CustomContext(contextOptions);
var clientHouseKeys = (await context2.Buildings
        .Where(building => building.Name == buildingName)
        .ToListAsync())
    .SelectMany(building => building.HouseKeys);
var serverHouseKeys = await context2.Buildings
    .Where(building => building.Name == buildingName)
    .SelectMany(building => building.HouseKeys)
    .ToListAsync();

Console.WriteLine($"client eval: {string.Join(", ", clientHouseKeys)}");
Console.WriteLine($"server eval: {string.Join(", ", serverHouseKeys)}");

public class CustomContext : DbContext
{
    public CustomContext(DbContextOptions<CustomContext> contextOptions) : base(contextOptions)
    {
    }

    public DbSet<Building> Buildings { get; set; }
}

public class Building
{
    public string Id { get; set; }
    public string Name { get; set; }
    public List<string> HouseKeys { get; set; }
}

And the related csproj:

<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net8.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
    </PropertyGroup>
    <ItemGroup>
        <PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.8" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="8.0.8" />
    </ItemGroup>
</Project>

Include stack traces

Unhandled exception. System.InvalidOperationException: The LINQ expression 'EF.Property<List<string>>(StructuralTypeShaperExpression: 
    Building
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
, "HouseKeys")' 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.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) 
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelectMany(ShapedQueryExpression source, LambdaExpression collectionSelector, LambdaExpression resultSelector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) 
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) 
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   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:\work\temp\Repro_EFCore\Program.cs:line 24
   at Program.<Main>$(String[] args) in C:\work\temp\Repro_EFCore\Program.cs:line 29
   at Program.<Main>$(String[] args) in C:\work\temp\Repro_EFCore\Program.cs:line 29
   at Program.<Main>(String[] args

Include version information

Microsoft.Data.Sqlite version: Microsoft.EntityFrameworkCore.Sqlite, v8.0.8
Target framework: .NET 8 (v8.0.303)
Operating system: Win 11 (23H2)

[roji]

Duplicate of #32505

[roji]

This is a bug in 8.0 that we already fixed in 9.0.0 (#32505) - please try one of the 9.0 previews (note that 9.0.0-rc.1 will be out in a few weeks). The fix wasn't trivial here, and since we haven't received much user feedback on this we didn't backport the fix.

[mu88]

Thx @roji for the quick feedback and sry for filing the duplicate - I was searching for the error message across the repo but couldn't find anything