Join with composite key - error: The LINQ expression ... could not be translated

[chrisarmand]

1. Description

I'm trying to write a JOIN query involving composite key, but cannot find proper syntax... help/advice would be welcome

Strongly typed exemple:

_context.Table1
.Join(_context.Table2,
    it1 => new { key1 = it1.Prop1, key2 = it1.Prop2 },
    it2 => new { key1 = it2.Prop1, key2 = it2.Prop2 },
    (it1, it2) => new { it1, it2 })

Dynamic LINQ trial:

_context.Table1
.Join(_context.Table2,
    "new(Prop1 as key1, Prop2 as key2)",
    "new(Prop1 as key1, Prop2 as key2)",
    "new(outer as t1, inner as t2)")
...

on execution, I keep getting following error: The LINQ expression ... could not be translated

2. Exception

The LINQ expression 'DbSet<Table1>()
    .Join(
        inner: DbSet<Table2>(), 
        outerKeySelector: c => new <>f__AnonymousType0<string, string>{ 
            key1 = c.Prop1, 
            key2 = c.Prop2 
        }
        , 
        innerKeySelector: s => new <>f__AnonymousType0<string, string>{ 
            key1 = s.Prop1, 
            key2 = s.Prop2 
        }
        , 
        resultSelector: (c, s) => new TransparentIdentifier<Table1, Table2>(
            Outer = c, 
            Inner = s
        ))' 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.

call stack

   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   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.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.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.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.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)

[StefH]

Does the strongly typed example work?

[chrisarmand]

Yes it does
https://learn.microsoft.com/en-us/dotnet/csharp/linq/join-by-using-composite-keys

[StefH]

@chrisarmand
Can you please provide a full working console-app or dotnetfiddle?

[chrisarmand]

Attached is a full working console-app - it relies on EFCore 7 + Postgresql provider

The strongly typed version works fine

dbcontext.Table1
.Join(dbcontext.Table2,
    it1 => new { key1 = it1.Prop1, key2 = it1.Prop2 },
    it2 => new { key1 = it2.Prop1, key2 = it2.Prop2 },
    (it1, it2) => new { it1, it2 })

and generate expected SQL query

SELECT t."Id", t."Prop1", t."Prop2", t0."Id", t0."Prop1", t0."Prop2"
FROM "Table1" AS t
INNER JOIN "Table2" AS t0 ON t."Prop1" = t0."Prop1" AND t."Prop2" = t0."Prop2"

The dynamic version

dbcontext.Table1
.Join(dbcontext.Table2,
    "new(Prop1 as key1, Prop2 as key2)",
    "new(Prop1 as key1, Prop2 as key2)",
    "new(outer as t1, inner as t2)")

raises following error

System.InvalidOperationException: The LINQ expression 'DbSet<Table1>()
    .Join(
        inner: DbSet<Table2>(), 
        outerKeySelector: t => new <>f__AnonymousType0<int, int>{ 
            key1 = t.Prop1, 
            key2 = t.Prop2 
        }
        , 
        innerKeySelector: t0 => new <>f__AnonymousType0<int, int>{ 
            key1 = t0.Prop1, 
            key2 = t0.Prop2 
        }
        , 
        resultSelector: (t, t0) => new TransparentIdentifier<Table1, Table2>(
            Outer = t, 
            Inner = t0
        ))' 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.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.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 Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.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.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.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Program.<Main>$(String[] args) in C:\Users\ChristopheARMAND\source\repos\zzzprojects-System.Linq.Dynamic.Core-issues-663\zzzprojects-System.Linq.Dynamic.Core-issues-663\Program.cs:line 29

zzzprojects-System.Linq.Dynamic.Core-issues-663.zip