Entity Framework Core: using non-anonymous type for outer/inner key selector

Question

I have many "join" tables in my data model, implementing many-to-many relationships, and these are having composite primary keys, one for each side of the relationship. I have for example room entity and document entity, and room-document table (obviously) has room_id and document_id. I also have product entity and similarly product-document table with product_id and document_id, and so on.

In EF Core 8 (and Npgsql.EntityFrameworkCore.PostgreSQL), I am creating a generic service which would work accross all types of a kind, for example working with all xxx-document types. Starting from non-generic version bound to rooms:

IQueryable<(int MainEntityId, int DocumentId)> someBaseQuery = /* omitted */;

var result = await
  someBaseQuery
  .LeftJoin // LeftJoin is just an extension on IQueryable<T> with GroupJoin + SelectMany
  (
    context.Set<RoomDocument>(),
    left => new { left.MainEntityId, left.DocumentId }, // outer-key selector
    right => new { MainEntityId = right.RoomId, right.DocumentId } // inner-key selector
  )
  .ToListAsync();

When making the service generic, in the inner-key selector right.xxxId would become heterogeneous, so I need to build the lambda expression dynamically (this is fine). But to keep the code readable I wanted to build the expression in another method, so anonymous types won't work, as lambda has to be the return value of the method.

One obvious choice is creating a composite type:

record Composite
{
  public int MainEntityId { get; init; }
  public int DocumentId { get; init; }
}

(remark: for record, one has to use member initialization, when using default constructor, EF cannot track properties further. This might be fine for final result, but if need to work further, one has to use this, or class+property/field syntax)

So the result becomes:

  .LeftJoin
  (
    context.Set<TEntityDocumentMapping>(),
    left => new Composite { MainEntityId = left.MainEntityId, DocumentId = left.DocumentId },
    GetKeySelectorExpression<TEntityDocumentMapping>() // returns Composite
  )

This seems nice, but unfortunately will then result in InvalidOperationException: "The LINQ expression ... could not be translated". The reason is that EF needs to translate the expression to SQL and cannot deal with my Composite type (ref.: https://github.com/dotnet/efcore/issues/25075#issuecomment-858807805)

I've also read: https://github.com/dotnet/efcore/issues/10784 and https://github.com/dotnet/efcore/issues/23775 and tried using configurationBuilder.Properties<CompositeKey>().HaveConversion, but so far no luck. Any suggestions?

Answer 1

I have a working solution using Tuple. I'm aware I could also create anonymous type expressions, for example as explained here: https://stackoverflow.com/a/3740637/6805011 but resulted in much longer and uglier code. So the simpler solution is using tuple:

var result = await someBaseQuery
  .LeftJoin
  (
    context.Set<TEntityDocumentMapping>(),
    left => ValueTuple.Create(left.MainEntityId, left.DocumentId),
    GetKeySelectorExpression<TEntityDocumentMapping>() // returns ValueTuple<int, int>
  )

Remark: tuple literals are not supported in expressions, but constructor or Create work just as fine.

One slight drawback is that one must use correct order in tuples, where anonymous or named object compiler helps with properties names.