Using non anonymous type inside outer/inner key selector of Join causes an error

[nemo048]

Hello!

I have a problem with Join operation between some entities.

Assuming I have following entities:

public sealed class Parent
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ChildId { get; set; }
}

public sealed class Child 
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
}

And a CompositeKey class:

public sealed class CompositeKey
{
    public int? NullableId { get; set; }

    private bool Equals(CompositeKey other)
    {
        return NullableId == other.NullableId;
    }

    public override bool Equals(object obj)
    {
        return ReferenceEquals(this, obj) || obj is CompositeKey other && Equals(other);
    }

    public override int GetHashCode()
    {
        return NullableId.GetHashCode();
    }
}

Parent and Child entities are in my DbContext. I want to join them as following:

dbContext.Set<Parent>()
    .Join(
        dbContext.Set<Child>(),
        parent => new
        {
            Id = parent.ChildId
        },
        child => new
        {
            Id = (int?)child.Id
        },
        (parent, child) => new
        {
            parentName = parent.Name,
            childName = child.Name
        })

It works fine. But if I use non anonymous type CompositeKey in both key selectors it causes an error (The LINQ expression could not be translated)

dbContext.Set<Parent>()
    .Join(
        dbContext.Set<Child>(),
        parent => new CompositeKey()
        {
            Id = parent.ChildId
        },
        child => new CompositeKey()
        {
            Id = child.Id
        },
        (parent, child) => new
        {
            parentName = parent.Name,
            childName = child.Name
        })

The error looks like this:

The LINQ expression 'DbSet<Parent>()
    .Join(
        inner: DbSet<Child>(), 
        outerKeySelector: d => new CompositeKey{ Id = d.ChildId }
        , 
        innerKeySelector: p => new CompositeKey{ Id = p.Id }
        , 
        resultSelector: (d, p) => new TransparentIdentifier<Parent, Child>(
            Outer = d, 
            Inner = p
        ))' 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.

Could you please help me. Am I doing something wrong or there is a limitation for using non anonymous types inside Join key selectors?

EF Core version: 5.0.6
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET 5.0
Operating system: Windows 10 (20H2)
IDE: Jetbrains Rider 2021.1.3

[smitpatel]

In order for join key selector to be translated to server as join predicate, join key needs to be either one of below

• A type which is supported by the database like a scalar type e.g. string/int/datetime
• A type which is mapped by provider through use of value converter.
• A C# anonymous type when join key is a composite key, which is decomposed into member-wise comparison by EF Core (keeping it aligned with how Equals work for anonymous type in client side)

EF Core doesn't know how comparison of CompositeKey works and cannot translate above query.

[nemo048]

@smitpatel Thank you for your answer! 😄 Also there is no way to use my CompositeKey class or I can do it with implementing IEqualityComparer or something like that?

[AndriySvyryd]

#25084 will allow specifying a value converter for it:

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder.Properties<CompositeKey>().HaveConversion<CompositeKeyConverter, CompositeKeyComparer>();
}

[balazsmeszegeto]

#25084 will allow specifying a value converter for it:

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder.Properties<CompositeKey>().HaveConversion<CompositeKeyConverter, CompositeKeyComparer>();
}

I could not make this (ie. creating arbitrary CompositeKey type and use it for join key-selector) work using EF Core 8. Coould you show a more detailed, working example?

BUT, if you, @nemo048, are only looking to avoid anonymous types, such as me (for example to pass/return to/from method or generating expressions dynamically, etc.), tuples work great!

dbContext.Set<Parent>()
    .Join(
        dbContext.Set<Child>(),
        parent => ValueTuple.Create(parent.ChildId),
        child => ValueTuple.Create(child.Id),
        (parent, child) => new
        {
            parentName = parent.Name,
            childName = child.Name
        })

[blemasle]

@balazsmeszegeto
I'm a bit confused as to how you got it to work. No matter what I do, I get Translation of method 'System.ValueTuple.Create' failed. If this method can be mapped to your custom function ..., despite the fact that I simplified the expression to its bare minimum with the selection of two int values.

Could you elaborate or maybe enlight me about what I'm missing ? 🙂

[balazsmeszegeto]

@blemasle I use Npgsql.EntityFrameworkCore.PostgreSQL. But my understanding was that tuples are core features of efcore and work accross all providers.

Maybe the static method ValueTuple.Create is not mapped in all providers, but did you try then standard constructor new ValueTuple<int, int>(...) or new Tuple<int, int>(...)?

[roji]

IIRC tuples and ValueTuple.Create() specifically are only supported in the PostgreSQL provider (and even there, only in very specific, retricted contexts). We do indeed plan to extend support but that's the current situation.

@blemasle @balazsmeszegeto what's the reason for wanting to avoid anonymous types and using ValueTuple.Create instead? Regardless, specifically in the above example, that's a single-value tuple ( ValueTuple.Create(parent.ChildId)) which seems even more odd (why need a tuple/anonymous object at all)?

[balazsmeszegeto]

Thanks for the update.

I also find the above example with single value odd, but having multiple values for a join is absolutely likely. One reason to avoid anonymous type is to utilize compiler support when creating expressions (see my SO link below)

Still, I found this thread and in particular comment from @smitpatel #25075 (comment) was quite useful when faced the issue given in the title, it helped me to understand and resolve the issue. I've created a Q&A about this: https://stackoverflow.com/questions/78790038/entity-framework-core-using-non-anonymous-type-for-outer-inner-key-selector

[roji]

Looking at the SO, it seems that you basically prefer tuples over anonymous types because you're saying it's simpler to dynamically generate them in LINQ expression trees... I'm not sure why that's the case - both should be more or less similar. In any case, ValueTuple.Create() isn't something that's supported across all EF providers, in contrast to anonymous types.

[blemasle]

IIRC tuples and ValueTuple.Create() specifically are only support in the PostgreSQL provider

That explains why it's not working for me, I'm using SQL Server.

what's the reason for wanting to avoid anonymous types

Long story short, I cannot use an anonymous type because the key expression must be supplied externally to the code calling Join. And as anonymous types cannot be declared I cannot pass the expression in a property or something similar.

It works when the join key is composed of only one value because in that case you can declare an Expression<Func<TInner, int>> for instance and pass it along. But as soon as you have a composite key you're stuck 😕 ValueTuple would have made the thing possible even if it would still be kind of ugly.

[balazsmeszegeto]

Try using HaveConversion in model configuration, as suggested above. If you manage, could you please share a working example?

[blemasle]

Try using HaveConversion in model configuration, as suggested above. If you manage, could you please share a working example?

Not sure to understand how to convert it so it would be understandable by EF. Convert it to what ? The type is a composite, so it cannot be converted to a simple type that would be understood by the EF provider 🤔

[blemasle]

Sorry I wasn't inspired to find a good simple use case, so names and properies are just here to made things easier to talk about but do not make much sense.

What I try to achieve is essentially the NonWorkingJoinAsync, where one of the key selector is supplied as a property.
Ideally, only one of the value composing the key would be provided by that property but that would make things even more impossible as it would mean building the expression tree dynamically, which if I'm not mistaken cannot be done with Join as we need a strongly typed expression.

Example code

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System.Linq.Expressions;

var services = new ServiceCollection()
    .AddDbContext<MyDbContext>(opts =>
    {
        opts.UseSqlite("Data Source=LocalDatabase.db");
    });

var serviceProvider = services.BuildServiceProvider();
var dbContext = serviceProvider.GetRequiredService<MyDbContext>();

await dbContext.Database.EnsureCreatedAsync();

var addresses = await WorkingJoinAsync(dbContext);
var addresses2 = await NonWorkingJoin(dbContext, a => new JoinKey { Id = a.Id, Type = a.Type });

async Task<IReadOnlyCollection<Place>> WorkingJoinAsync(MyDbContext dbContext)
{
var places = await dbContext.Addresses
    .Join(
        dbContext.Places,
        a => new { a.Id, a.Type },
        p => new { p.Id, p.Type },
        (a, p) => p)
    .ToArrayAsync();

return places;
}

async Task<IReadOnlyCollection<Place>> NonWorkingJoin(MyDbContext dbContext, Expression<Func<Address, JoinKey>> exp)
{
var places = await dbContext.Addresses
    .Join(
        dbContext.Places,
        exp,
        p => new JoinKey { Id = p.Id, Type = p.Type },
        (a, p) => p)
    .ToArrayAsync();

return places;
}

class JoinKey
{
    public int Id { get; set; }
    public LocationType Type { get; set; }
}

internal class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> optionsBuilder)
    : base(optionsBuilder)
    {
    }

    public DbSet<Place> Places { get; set; }
    public DbSet<Address> Addresses { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<Place>();

        modelBuilder
            .Entity<Address>();
    }
}

internal class Place
{
    public int Id { get; set; }
    public string Name { get; set; }
    public LocationType Type { get; set; }
}

internal class Address
{
    public int Id { get; set; }
    public string Value { get; set; }
    public LocationType Type { get; set; }
}

internal enum LocationType
{
    Home,
    Business

}

[balazsmeszegeto]

This is all I got: https://learn.microsoft.com/en-us/ef/core/modeling/value-comparers#key-comparers

We'd need to compare, but the samples from this page leads to converters. The best match is CompositeValueObject and it converts to Json, which is not exactly what we'd looking for:
https://github.com/dotnet/EntityFramework.Docs/blob/main/samples/core/Modeling/ValueConversions/CompositeValueObject.cs#L48C18-L50C93

[balazsmeszegeto]

I've tried updating your sample:

internal class MyDbContext : DbContext
{
//...
    protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
    {
        configurationBuilder.Properties<JoinKey>().HaveConversion<IrreleventConverter, JoinKeyComparer>();
    }
    
}
class JoinKeyComparer() : ValueComparer<JoinKey>((x, y) => x.Id == y.Id && x.Type == y.Type, x => HashCode.Combine(x.Id, x.Type));

Gives the same result. Tried to examine during runtime and observed that JoinKeyComparer never gots instantiated. Looking into the runtime model (dbContext.Model), did not find the registered comparer anywhere. Makes me wonder, is it only possible to register converter for mapped properties of an entity? In our case, JoinKey is not a mapped property and only used to define an "ad-hoc" join expression

The following comment indicates that type mapping might be different than Property configuration:

A type which is mapped by provider through use of value converter.
#25075 (comment)

[blemasle]

I found a workaround using a template Expression and an ExpressionVisitor.
It's not a generic solution per say but for my actual use case where everything is controlled before hand it is acceptable.

I left it in that public gist in case it could help anyone.

[balazsmeszegeto]

Thanks!

I'd still appreciate if someone with better knowledge about value comparers could enlighten us how should the above example (JoinKey) could work.

[ldeluigi]

I don't understand why this feature is not supported. Imagine having to repeat tens or hundreds of times the same expression that creates an anonymous type because you can't reuse an expression whose return type is well defined, once and safely.

[roji]

@ldeluigi I don't follow how doing this would help you avoid repeating anything - can you please post the exact code you expect to be able to write?

[ldeluigi]

We have a number of tables with a composite primary key of n fields, now n is 5 but could grow bigger in the future. We have many queries joining multiples of these tables through their primary key, sometimes with all the n fields and sometimes more or less. We also use LinqKit to reuse the same expressions for our queries as much as possible. We have made a POCO aka complex type to store those keys inside queries, as currently we can't use complex types as primary keys.

Nevertheless, we are avoiding much repetition by passing or invoking the same expressions inside as many queries as possible, but we can't do that inside join key expressions. The reason is that even if we declare an expression like

public static Expression<Func<IHaveACompositeKey, CompositeKeyModel>> KeySelector { get; } = m => new CompositeKeyModel
{
    Field1 = m.Field1,
    Field2 = m.Field2,
    Field3 = m.Field3,
    Field4 = m.Field4,
    Field5 = m.Field5,
};

We can't pass it in either side of the join in linq, because the join only works with anonymous types or scalars.

Let me know if I explained myself well enough. As a consequence, we need to write the full expression every time we do a join. Another downside is that if we add a new element to the composite key, our anonymously typed expression would still compile, even though in many cases we would like to have a compile error because the key used in the join is not valid and complete anymore, something achievable only with our user defined type CompositeKeyModel

[roji]

@ldeluigi thanks, that's clear indeed, and more or less what @blemasle wrote here: #25075 (comment).

As @smitpatel wrote above, the problem with allowing arbitrary POCOs, is that join keys are there in order to be compared, and EF cannot support (or even know) the comparison logic that's defined on an arbitrary .NET type. EF could assume that all public properties simply need to be compared as-is, but that's quite an assumption and I don't think we want to go down this path: we'll have users asking us why their Equals implementation isn't respected, or why their private fields aren't being taken into account, etc.

But the point about not being able to declare methods/properties that return key selectors returning anonymous types is valid. I do think that's a good argument for allowing tuples to be used as join keys: they have clearly defined comparison semantics, have a name (and so can be returned and therefore shared), etc. I've opened #35565 to track this.