efcore: Using non anonymous type inside outer/inner key selector of Join causes an error
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
About this issue
- Original URL
- State: closed
- Created 5 years ago
- Reactions: 1
- Comments: 21 (6 by maintainers)
@ldeluigi thanks, that’s clear indeed, and more or less what @blemasle wrote here: https://github.com/dotnet/efcore/issues/25075#issuecomment-2304590945.
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.
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
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