EF Core 6 - Unable to translate a collection subquery in a projection

[arthur-liberman]

I'm porting our code from .NET Core 3.1 to .NET 6. The issue seems to be a regression from our POV, because it used to work correctly in 3.1 and also in 2.1 if I remember correctly.
We have a query constructed from a table left joined with a view. There's a one-to-many navigation property tied to this table.
If I access the list when constructing the DTO, I get the following exception:

System.InvalidOperationException: Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly generate results on the client side. This can happen when trying to correlate on keyless entity type. This can also happen for some cases of projection before 'Distinct' or some shapes of grouping key in case of 'GroupBy'. These should either contain all key properties of the entity that the operation is applied on, or only contain simple property access expressions.
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyProjection(Expression shaperExpression, ResultCardinality resultCardinality, QuerySplittingBehavior querySplittingBehavior)
   at Microsoft.EntityFrameworkCore.Query.Internal.SelectExpressionProjectionApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   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)

If I join the table used by the view instead, everything works fine.

To reproduce:
csproj:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <RootNamespace>efcore_demo</RootNamespace>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.5" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="6.0.5" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="6.0.5" />
    <PackageReference Include="Microsoft.Extensions.Logging.Console" Version="6.0.0" />
  </ItemGroup>

</Project>

Code:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.Logging;

namespace efcore_demo
{
    class Program
    {
        static void Main(string[] args)
        {
            var seeder = new Seeder();
            seeder.Seed();
            var logic = new Logic();
            var dtos = logic.ExecuteGoodQuery();
            dtos = logic.ExecuteFailedQuery();
            Console.WriteLine("Hello World!");
        }
    }

    public class Logic
    {
        public IList<Dto> ExecuteGoodQuery()
        {
            using (var dbContext = new MyDbContext())
            {
                return
                (from i in dbContext.Items
                 from cat in dbContext.Categories.Where(c => c.Id == i.Id).DefaultIfEmpty()
                 select new Dto
                 {
                     Id = i.Id,
                     Name = cat.Name,
                     Colors = String.Join(";", i.Colors.Select(c => c.Name)),
                 }).ToList();
            }
        }

        public IList<Dto> ExecuteFailedQuery()
        {
            using (var dbContext = new MyDbContext())
            {
                return
                (from i in dbContext.Items
                 from cat in dbContext.CreatedInCategory.Where(c => c.Id == i.Id).DefaultIfEmpty()
                 select new Dto
                 {
                     Id = i.Id,
                     Name = cat.Name,
                     Colors = String.Join(";", i.Colors.Select(c => c.Name)),
                 }).ToList();
            }
        }
    }

    public class Dto
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Colors { get; set; }
    }

    public class Seeder
    {
        public void Seed()
        {
            using (var dbContext = new MyDbContext())
            {
                var rdc = dbContext.Database.GetService<IRelationalDatabaseCreator>();
                if (rdc.Exists())
                    rdc.EnsureDeleted();
                rdc.EnsureCreated();
                dbContext.Database.ExecuteSqlRaw(@"CREATE VIEW CREATED_IN_CATEGORY
AS 
    SELECT Item.id                              AS VF_ID,
           (Category.name || '\' || Item.name)    AS VF_NAME
    FROM Item 
        JOIN Category
        ON Item.categoryId = Category.id;");
                if (!dbContext.Items.Any())
                {
                    for (int i = 0; i < 10; i++)
                    {
                        var rand = new Random();
                        int cat = rand.Next() % 3;
                        int colors = rand.Next() % 4;
                        var item = new Item { CategoryId = cat, Name = $"Item {i}" };
                        for (int c = 0; c <= colors; c++)
                        {
                            item.AddColor(c);
                        }
                        dbContext.Items.Add(item);
                    }
                }
                if (!dbContext.Categories.Any())
                {
                    for (int i = 0; i < 3; i++)
                    {
                        var category = new Category { Name = $"Category {i}" };
                        dbContext.Categories.Add(category);
                    }
                }
                dbContext.SaveChanges();
            }
        }
    }

    public class Item
    {
        private readonly List<Color> _colors = new List<Color>();
        public IReadOnlyCollection<Color> Colors => _colors;
        public int Id { get; set; }
        public int CategoryId { get; set; }
        public string Name { get; set; }

        internal void AddColor(int i)
        {
            var color = new Color { Name = $"Color {i}" };
            _colors.Add(color);
        }
    }

    public class Color
    {
        public int ItemId { get; private set; }
        public virtual Item Item { get; private set; }
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class Category
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class CreatedInCategory
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class MyDbContext : DbContext
    {
        public static readonly ILoggerFactory loggerFactory = LoggerFactory.Create(builder =>
        {
            builder.AddConsole();
        });

        public DbSet<Item> Items { get; set; }
        public DbSet<Color> Colors { get; set; }
        public DbSet<Category> Categories { get; set; }
        public DbSet<CreatedInCategory> CreatedInCategory { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfiguration(new ItemConfiguration());
            modelBuilder.ApplyConfiguration(new CategoryConfiguration());
            new CreatedInCategoryConfiguration().Configure(modelBuilder);
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseLoggerFactory(loggerFactory)
                .EnableSensitiveDataLogging()
                .UseSqlite("Data Source=efcoredemo.db");
        }
    }

    public class ItemConfiguration : IEntityTypeConfiguration<Item>
    {
        public void Configure(EntityTypeBuilder<Item> builder)
        {
            builder.ToTable("Item");
            builder.HasKey(o => o.Id);
            builder.Property(t => t.Id).HasColumnName("id").ValueGeneratedOnAdd();
            builder.Property(t => t.CategoryId).HasColumnName("categoryId");
            builder.Property(t => t.Name).HasColumnName("name");
        }
    }

    public class ColorConfiguration : IEntityTypeConfiguration<Color>
    {
        public void Configure(EntityTypeBuilder<Color> builder)
        {
            builder.ToTable("Color");
            builder.HasKey(o => o.Id);
            builder.Property(t => t.Id).HasColumnName("id").ValueGeneratedOnAdd();
            builder.Property(t => t.Name).HasColumnName("name");

            builder.HasOne(s => s.Item)
                .WithMany(s => s.Colors)
                .HasForeignKey(s => s.ItemId);
        }
    }

    public class CategoryConfiguration : IEntityTypeConfiguration<Category>
    {
        public void Configure(EntityTypeBuilder<Category> builder)
        {
            builder.ToTable("Category");
            builder.HasKey(o => o.Id);
            builder.Property(t => t.Id).HasColumnName("id").ValueGeneratedOnAdd();
            builder.Property(t => t.Name).HasColumnName("name");
        }
    }

    public class CreatedInCategoryConfiguration
    {
        public void Configure(ModelBuilder builder)
        {
            var queryBuilder = builder.Entity<CreatedInCategory>().HasNoKey();
            queryBuilder.ToView("CREATED_IN_CATEGORY");
            queryBuilder.Property(t => t.Id).HasColumnName("VF_ID");
            queryBuilder.Property(t => t.Name).HasColumnName("VF_NAME");
        }
    }
}

As mentioned above, in 3.1.11 we did not have this problem.
Currently using the latest available .NET SDK, 6.0.5.

[ajcvickers]

Note from triage: The 3.1 behavior generated incorrect results. @maumar to look for relevant issue.

[maumar]

relevant issue: #15873

[arthur-liberman]

Sorry, I'm trying to understand the replies.
Are you saying that 3.1 was not working correctly and 6.0 is behaving as expected?
Will 3.1 be fixed?
Is a fix planned for 6.0 or later?

[ajcvickers]

@arthur-liberman Yes, 3.1 was not failing, but the SQL generated returned incorrect results. 6.0 now returns correct results where it can, or throws when the translation is not possible. You should consider changing your queries so that they can be translated; @smitpatel or @maumar may be able to provide some pointers.

It's very unlikely anything will change here in either 3.1 or 6.0.

[cmbkla]

@arthur-liberman I am also fighting this issue, trying to update from 3.1 to 6. I keep seeing this "returned incorrect results" reply, but it's objectively not the case that the results were ever inaccurate. This pattern has been in production for over a year in a mission-critical application, if it was inaccurate we'd have a bug about it. I am not sure what this "inaccurate" comment means, and it doesn't help the broken code.

Same situation -- the code in question for us doesn't use Distinct or GroupBy whatsoever.

I've isolated the problematic spots, and based on the answers I have been seeing, it sounds like the actual response here is "re-write your code". It's only months of development, nbd.

[arthur-liberman]

@cmbkla agreed. In our use case we have not observed any issues.
Perhaps there are certain situations where the translation should not be possible, but the framework creates an incorrect translation. But I have not seen this issue.

We are still working on getting the port finished, so have not performed a full system test yet.
In my specific case there was an easy workaround/fix for this issue, so it doesn't really affect us much.
But I feel for your pain.

[ajcvickers]

@smitpatel to detail cases where we cannot translate.

[smitpatel]

Wrote detailed response in #23830 (comment)

This is by design. It cannot generate correct results in all scenario. The correct results happens only in certain kind of data by co-incidence. Any change in underlying data can start generating wrong results.