Translation error for query with Distinct on collection

[imb590]

The following code throws InvalidOperationException on EF Core 5.0.2

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

class Program
{
    static void Main(string[] args)
    {
        using var db = new Context();

        db.Database.EnsureDeleted();
        db.Database.EnsureCreated();

        var result = db.Ones
            .Select(o => new
            {
                threes = o.Twos!.Select(t => t.Three).Distinct().Select(t => t!.Id),
            })
            .ToList();
    }
}

public class Context : DbContext
{
    private static ILoggerFactory LoggerFactory => new ServiceCollection().AddLogging(l => l.AddConsole().SetMinimumLevel(LogLevel.Trace)).BuildServiceProvider().GetRequiredService<ILoggerFactory>();

    public DbSet<EntityOne> Ones { get; set; } = null!;

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlite("filename=test.db")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(LoggerFactory);
    }
}

public class EntityOne
{
    public int Id { get; set; }

    [InverseProperty(nameof(EntityTwo.One))]
    public ICollection<EntityTwo>? Twos { get; set; }
}

public class EntityTwo
{
    public int Id { get; set; }

    public int OneId { get; set; }

    public EntityOne? One { get; set; }

    public int ThreeId { get; set; }

    public EntityThree? Three { get; set; }
}

public class EntityThree
{
    public int Id { get; set; }
}

System.InvalidOperationException: Unable to translate collection subquery in projection since the parent query doesn't project key columns of all of it's tables which are required to generate results on client side. This can happen when trying to correlate on keyless entity or when using 'Distinct' or 'GroupBy' operations without projecting all of the key columns.
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyCollectionJoin(Int32 collectionIndex, Int32 collectionId, Expression innerShaper, INavigationBase navigation, Type elementType, Boolean splitQuery)
   at Microsoft.EntityFrameworkCore.Query.Internal.CollectionJoinApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitNew(NewExpression node)
   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.CollectionJoinApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Sqlite.Query.Internal.SqliteQueryTranslationPostprocessor.Process(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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Program.Main(String[] args)

The query is translated correctly by EF Core 3.1:

      SELECT "o"."Id", "t"."Id"
      FROM "Ones" AS "o"
      OUTER APPLY (
          SELECT DISTINCT "e0"."Id"
          FROM "EntityTwo" AS "e"
          INNER JOIN "EntityThree" AS "e0" ON "e"."ThreeId" = "e0"."Id"
          WHERE "o"."Id" = "e"."OneId"
      ) AS "t"
      ORDER BY "o"."Id", "t"."Id"

[anranruye]

1. If EF Core 3.1 meets all you requirements, you can always use it and have no need to update to ef core 5. I think the ef team has their reason to disable a query which works in previous version.(although I don't know what it is). If you decide to update to the new version, then you must face these known or unknown breaking changes.

2. From your sample, I can't see the reason to use the Distinct() method. It seems that EntityTwo is the join entity between EntityOne and EntityThree. If so, then there should not be two EntityTwo object which share the same OneId and ThreeId(you can make OneId and ThreeId as alternate key to ensure this). Also, you can take advantages of the new many-to-many relationship introduced by ef core 5.

[imb590]

The reason to use Distinct() is, as the name says, to get distinct rows from the database. EntityTwo is not the join entity, there can be different EntityTwos belonging to the same EntityOne and having the same EntityThree, hence the need for Distinct() if the repeating values are not desired in the output.

[anranruye]

@MBashov I tried some approaches to perform a query using 'distinct' statement with ef core 5, but they all failed. Then I changed my mind and got another idea.

You can use ToHashSet() to remove duplicate items during ef core handling the data from the database:

        var result = db.Ones
            .Select(o => new
            {
                threes = o.Twos!.Select(t => t.Three.Id).ToHashSet().AsEnumerable(),
            })
            .ToList();

Update: In fact the call to ToHashSet() is client evaluation, you can only use it in the last select statement.

[maumar]

related to #22049