Case Insensitive string comparison in Composite Foreign Keys

[Hylaean]

I fond an issue in a model were navigability of entities didn't work when casing changed between the target and source tables.
When a case insensitive comparer is added, it seems the inner and outer key selectors can't be translated.

Exception:

   System.InvalidOperationException : The LINQ expression 'DbSet<Product>()
        .Where(p => p.SubscriptionId == __subscriptionId_0 || p.SubscriptionId == -1)
        .Where(p => p.ProductCode == __productCode_1 && !(p.IsDeleted))
        .Join(
            inner: DbSet<ProductDefinition>(), 
            outerKeySelector: p => new object[]
            { 
                (object)EF.Property<int?>(p, "SubscriptionId"), 
                (object)EF.Property<string>(p, "ProductCode"), 
                (object)EF.Property<string>(p, "CustomerCode") 
            }, 
            innerKeySelector: p0 => new object[]
            { 
                (object)EF.Property<int?>(p0, "SubscriptionId"), 
                (object)EF.Property<string>(p0, "ProductCode"), 
                (object)EF.Property<string>(p0, "CustomerCode") 
            }, 
            resultSelector: (o, i) => new TransparentIdentifier<Product, ProductDefinition>(
                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)

version information

EF Core version: 6.0.2
Database provider: *
Target framework: .NET 6.0

[ajcvickers]

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[leppie]

I ran into the same issue, this has been a bug since ef core 2 as I could repro on it. Was running on SQL server.

I suspect @Hylaean tried a case-insensitive comparer as a workaround, but IMO the join should be working without a special comparer.

So basically you have a composite key where one column has a foreign key.

Table A(name)
A
B
C

Table AB(name, code)
A, 1
A, 2
a, 3
A, 4
B, 1
C, 2
C, 1

When you get related AB's on A for name=A, only values returned are up to be not including the row where the casing changes. The rest are ignored. IOW:

A, 1
A, 2

If the casing was a, 1 (and all others uppercase), no rows are returned.

The queries are correct, but it seems when it joins in code, it does a case-sensitive comparison (seemingly iterating till the key does not match any more and stops).

@ajcvickers let me know if you need more info.

[Hylaean]

Thanks @leppie, sorry I haven't yet come about to fix this for good.

There is indeed the navigability issue, using a custom case insensitive comparer leads to EF Core not being able to generate the query when more two or more properties are needed for the join.

[leppie]

Here is a blog post about this issue and perhaps more details

https://qszhuan.github.io/dotnetcore/2018/10/03/dotnetcore-foreign-key-case-sensitive

[leppie]

Here is the solution/workaround: https://docs.microsoft.com/en-us/ef/core/modeling/value-conversions?tabs=data-annotations#use-case-insensitive-string-keys

[Hylaean]

Thanks. I was aware of this. The issue now is when I inherited a database where composite keys were used as foreign keys. Both CI strings. Then the mapping doesn't work. EF doesn't know how to build the query in such a case.

…

On Wed, Feb 23, 2022, 07:04 Llewellyn Pritchard ***@***.***> wrote: Here is the solution/workaround: https://docs.microsoft.com/en-us/ef/core/modeling/value-conversions?tabs=data-annotations#use-case-insensitive-string-keys — Reply to this email directly, view it on GitHub <#27467 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAJ64RA5IY23NS2SOSFKUZDU4R2HDANCNFSM5OVSDPXQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[roji]

Duplicate of dotnet/EntityFramework.Docs#2979

[roji]

Setting up a value comparer is indeed the right way to work around this.

[Hylaean]

I'm sorry @roji, my point is that I did that and setting up a value comparer leads to exception mentioned when the foreign key looks like that.

        .HasPrincipalKey(d => new { d.SubscriptionId, d.ProductCode, d.CustomerCode })
        .HasForeignKey(d => new { d.SubscriptionId, d.ProductCode, d.CustomerCode })

I've forked the repo trying to find a unit test I could use as inspiration to demonstrate this bug. Can you point me to one?

[roji]

@Hylaean sorry, I didn't notice your comment above.

But in any case, what we're missing here is a full, runnable code sample - it's difficult to understand from the above bits and pieces exactly what's going on. Ideally you would just write a simple console program that reproduces the error; if there's a problem in your code (as opposed to in EF), this would also help you isolate it.

[Hylaean]

Sorry @roji I hadn't time to reproduce this outside.
Ok, so I think the following tests illustrate the issue:

#nullable disable
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
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; }
    }
    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 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().Metadata.SetValueComparer(Comparers.CI);
                entity.Property(e => e.FamilyName).IsRequired().Metadata.SetValueComparer(Comparers.CI);
            });


            model.Entity<Specy>(entity =>
            {
                entity.HasKey(e => new { e.Name, e.GenusName, e.FamilyName });
                entity.Property(e => e.Name).IsRequired().Metadata.SetValueComparer(Comparers.CI);
                entity.Property(e => e.FamilyName).IsRequired().Metadata.SetValueComparer(Comparers.CI);
                entity.Property(e => e.GenusName).IsRequired().Metadata.SetValueComparer(Comparers.CI);
                entity.HasOne(e => e.Genus).WithMany()
                    .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 = taxo.Species.ToList();
    }

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

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

[roji]

Duplicate of #27495

[ajcvickers]

See also #27526