Complex value comparer expressions can not be translated by the in-memory query pipeline

[Hylaean]

Exception when querying entities linked by case insensitive string comparison in composite foreign keys

When using custom comparers on fields used in a composite foreign key, some queries can't be generated and result in an InvalidOperationException.
The tests below describe both the bug and its limits.

#nullable disable
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Xunit;

namespace EfBug;

public class EFComparerCompositeKeyBug
{
    public class Genus
    {
        public string Name { get; set; }
        public string FamilyName { get; set; }
        public ICollection<Specy> Species { get; set; }
    }
    public class Specy
    {
        public string Name { get; set; }
        public string GenusName { get; set; }
        public string FamilyName { get; set; }
        public Genus Genus { get; set; }
    }

    public class TaxonomyContext : DbContext
    {
        protected override void OnModelCreating(ModelBuilder model)
        {
            model.Entity<Genus>(entity =>
            {
                entity.HasKey(e => new { e.Name, e.FamilyName });
                entity.Property(e => e.Name).IsRequired().CaseInsensitive();
                entity.Property(e => e.FamilyName).IsRequired().CaseInsensitive();
            });


            model.Entity<Specy>(entity =>
            {
                entity.HasKey(e => new { e.Name, e.GenusName, e.FamilyName });
                entity.Property(e => e.Name).IsRequired().CaseInsensitive();
                entity.Property(e => e.FamilyName).IsRequired().CaseInsensitive();
                entity.Property(e => e.GenusName).IsRequired().CaseInsensitive();
                entity.HasOne(e => e.Genus).WithMany(e => e.Species)
                    .HasPrincipalKey(e => new { GenusName = e.Name, e.FamilyName })
                    .HasForeignKey(e => new { e.GenusName, e.FamilyName });
            });

        }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            base.OnConfiguring(options);
            options.UseInMemoryDatabase("Taxonomy");
        }

        public virtual DbSet<Genus> Genera { get; set; } = null!;
        public virtual DbSet<Specy> Species { get; set; } = null!;

    }

    [Fact]
    public void Passes()
    {
        using var taxo = new TaxonomyContext();
        var species = (from g in taxo.Genera
                       where g.FamilyName == "Ornithorhynchidae"
                       from s in g.Species
                       select s).Distinct().ToList();
    }

    [Fact]
    public void Fails()
    {
        using var taxo = new TaxonomyContext();
        var ex = Assert.Throws<InvalidOperationException>(() => (from s in taxo.Species
                                                            where s.Genus.FamilyName == "Ornithorhynchidae"
                                                            select s).ToList());
        Assert.Contains("could not be translated", ex.Message);
    }

    [Fact]
    public void AlsoFails()
    {
        using var taxo = new TaxonomyContext();
        var ex = Assert.Throws<InvalidOperationException>(() => (from g in taxo.Genera
                                                                 where g.FamilyName == "Ornithorhynchidae"
                                                                 from s in g.Species
                                                                 select s).Include(s => s.Genus).Distinct().ToList());
        Assert.Contains("could not be translated", ex.Message);
    }
}

internal static class Comparers
{
    public static ValueComparer<string>? CI { get; } = new(
        (l, r) => string.Equals(l, r, StringComparison.OrdinalIgnoreCase),
        v => StringComparer.OrdinalIgnoreCase.GetHashCode(v)
        );

    public static PropertyBuilder<string> CaseInsensitive(this PropertyBuilder<string> property)
    {
        var md = property.Metadata;
        md.SetKeyValueComparer(CI);
        md.SetValueComparer(CI);
        return property;
    }
}

Exception

System.InvalidOperationException: 'The LINQ expression 'DbSet<Specy>()
    .Join(
        inner: DbSet<Genus>(), 
        outerKeySelector: s => new object[]
        { 
            (object)EF.Property<string>(s, "GenusName"), 
            (object)EF.Property<string>(s, "FamilyName") 
        }, 
        innerKeySelector: g => new object[]
        { 
            (object)EF.Property<string>(g, "Name"), 
            (object)EF.Property<string>(g, "FamilyName") 
        }, 
        resultSelector: (o, i) => new TransparentIdentifier<Specy, Genus>(
            Outer = o, 
            Inner = i
        ))' 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.'

  Stack Trace: 
    QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
    QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    InMemoryQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    MethodCallExpression.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    InMemoryQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
    MethodCallExpression.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)

Provider and version information

EF Core version: 6.0.2
Database provider: SqlServer / InMemory / more?
Target framework: .net 6.0.2
Operating system: W11

[ajcvickers]

Note for triage: query translation only fails on in-memory database, which is interesting since the in-memory database has case-sensitive keys anyway. So it's not clear how using case-insensitive key comparison for the state manager would work for the in-memory database which is case-sensitive.

[ajcvickers]

See also #27526

[Hylaean]

@ajcvickers It doesn't only crash in-memory. It crashes everywhere. I only used in-memory as it was easier to demonstrate there.

[ajcvickers]

@Hylaean I changed the code to use SQL Server and saw no exceptions. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing with the SQL Server or SQLite provider so that we can investigate.

[Hylaean]

@ajcvickers: you're right, there was a bug in our test suite that lead to this misconception. thanks.

[ajcvickers]

Note from triage: closing since this is the in-memory database.