Throw better message for subquery for entity with composite primary key

[Neme12]

Steps to reproduce

1. Start with this project file and a C# file:

   
   <Project Sdk="Microsoft.NET.Sdk">
   
   <PropertyGroup>
   <OutputType>Exe</OutputType>
   <TargetFramework>net5.0</TargetFramework>
   </PropertyGroup>
   
   <ItemGroup>
   <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.1" />
   </ItemGroup>

```C#
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;

namespace EfCore5Test
{
    public sealed class Course
    {
        public Guid Id { get; set; }

        public IList<CourseLocalization> Localizations { get; set; }
    }

    public sealed class CourseLocalization
    {
        public Guid CourseId { get; set; }
        public Course Course { get; set; }

        public string LanguageCode { get; set; }
    }

    public sealed class ApplicationDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=aspnet-WebApplication-6E483A89-BEE6-4A76-96CC-CEB276E5E112;Trusted_Connection=True;MultipleActiveResultSets=true");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<CourseLocalization>(builder =>
            {
                builder.HasKey(x => new { x.CourseId, x.LanguageCode });
                builder.HasOne(x => x.Course).WithMany(x => x.Localizations).HasForeignKey(x => x.CourseId).OnDelete(DeleteBehavior.Cascade);
            });
        }

        public DbSet<Course> Courses { get; set; }
        public DbSet<CourseLocalization> CourseLocalizations { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new ApplicationDbContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                context.Courses
                    .Select(x => new
                    {
                        Id = x.Id,
                        Localization = x.Localizations.FirstOrDefault(x => x.LanguageCode == "en-US"),
                    })
                    .Where(x => x.Localization != null)
                    .ToList();
            }
        }
    }
}

2. Run. An exception is thrown:

   Unhandled exception. System.InvalidOperationException: The LINQ expression 'Projection Mapping:
   EmptyProjectionMember -> [EntityProjectionExpression]
   SELECT TOP(1) c.CourseId, c.LanguageCode
   FROM CourseLocalizations AS c
   WHERE ((c.Id != NULL) && (c.Id == c.CourseId)) && (c.LanguageCode == N'en-US')' 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.SqlExpressions.ScalarSubqueryExpression.Verify(SelectExpression selectExpression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.ScalarSubqueryExpression..ctor(SelectExpression subquery)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.BindProperty(EntityReferenceExpression entityReferenceExpression, IProperty property)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TryBindMember(Expression source, MemberIdentity member)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitUnary(UnaryExpression unaryExpression)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitUnary(UnaryExpression unaryExpression)
   at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TryRewriteEntityEquality(ExpressionType nodeType, Expression left, Expression right, Boolean equalsMethod, Expression& result)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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 System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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 EfCore5Test.Program.Main(String[] args) in C:\Users\SimonaKonickova\source\repos\EfCore5Test\EfCore5Test\Program.cs:line 55

   This works properly in EF Core 3.1. And it seems like the problem is that CourseLocalization has composite primary key (which makes this look really similar to https://github.com/dotnet/efcore/issues/23401, which was fixed in 5.0.1. I hope it's not a more widespread issue across EF Core 😕)

I found a workaround to instead use Where(x => x.Localization.CourseId != null) (which generates a compiler warning though).

Include provider and version information

EF Core version: 5.0.1 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 5.0 Operating system: Windows 10 Version 1909 IDE: Visual Studio 16.9.0 Preview 2.0

[smitpatel]

There are several different factors here but main thing is

• EF Core avoids rewriting entity comparisons when entity is coming from a subquery and has a composite PK. Doing a rewrite to compare on key values would cause the subquery to be repeated (n times where n is number of properties in PK). Computation of subquery could be expensive (even though doing FirstOrDefault), if it has filter on a property which does not have index or it is uses an expensive database function in the filter. Hence we don't rewrite. Users can decompose the subquery entity comparison in PK properties comparison if they believe that subquery computation is not expensive.
• Hence the work-around you are using is right way to do. More accurate work-around would be Where(x => x.Localization.CourseId != null && x.Localization.LanguageCode != null) Any property in key being null means key is null so you would need to verify that each property is non-null.
• Above may generate a compiler warning as it would be comparing a non-nullable column to null. But compiler is not accounting that Localization can be null. To remove warning you can cast CourseId to nullable type in the query before comparing. In EF Core it would be removed when translating. There is no way to avoid compiler warning. If compiler takes that Localization can be null then property access is null ref. If it does not then non-nullable being compared to null. The semantics of EF Core and database are different than Linq so compiler fails to infer correctly.

@Neme12 - You should rewrite the query as suggested work-around in point 2 above. We cannot change the behavior back to working query in 3.1 as it was a bug which made it work (and probably gave correct results, but that may not be always correct)

Notes for triage

• Exception is happening in wrong place. We should fix this so it throws a better error message.
• We block subquery entity equality for comparison a == b. We don't do it for a == null though it has same issue. We should consider block this too.
• This worked in EF Core 3.1 because of the bug https://github.com/dotnet/efcore/issues/15080. We only checked first column but that is incorrect as we need to check all columns in above case as discussed on that issue.
• I think missing validation in point 2 became issue because we also implemented point 3 so now subquery entity == null also repeats the subquery and we should block it. This would probably fix the error in point 1.

[ajcvickers]

Discussed in triage and the conclusion is to throw a better exception message for this case.

[maumar]

we now translate this scenario, producing the following sql:

SELECT [c].[Id], [t0].[CourseId], [t0].[LanguageCode]
FROM [Courses] AS [c]
LEFT JOIN (
    SELECT [t].[CourseId], [t].[LanguageCode]
    FROM (
        SELECT [c1].[CourseId], [c1].[LanguageCode], ROW_NUMBER() OVER(PARTITION BY [c1].[CourseId] ORDER BY [c1].[CourseId], [c1].[LanguageCode]) AS [row]
        FROM [CourseLocalizations] AS [c1]
        WHERE [c1].[LanguageCode] = N'en-US'
    ) AS [t]
    WHERE [t].[row] <= 1
) AS [t0] ON [c].[Id] = [t0].[CourseId]
WHERE EXISTS (
    SELECT 1
    FROM [CourseLocalizations] AS [c0]
    WHERE [c].[Id] = [c0].[CourseId] AND [c0].[LanguageCode] = N'en-US')