Query with `groupjoin` cannot be translated although it’s documened as being supported

The explanation in the linked documentation just follows the EF Core team vision and is ridiculous, because of course it can easily be translated – I had a long discussion with the team here Query with GroupBy or GroupJoin throws exception #17068 and continue here Query: Support GroupJoin when it is final query operator #19930, trying to convince them why it should be supported, with no luck regardless of the arguments.

The whole point is (and that’s the current workaround) it can be processed like if it was correlated subquery (SelectMany), which is translated and processed properly (even though the query result shape has no SQL equivalent.

Anyway, the current status is “Needs Design” (whatever that means), and the workaround is to replace the join with correlated subquery (which is what EF Core is using internally when “expanding” collection navigation properties during the query translation).

In your case, replace

join b in ctx.Bs on a.aId equals b.aId into bs

with

let bs = ctx.Bs.Where(b => a.aId == b.aId)

However, I highly recommend adding and using navigation properties. Not sure why you “can’t use” them, in LINQ to Entities which do not project entities they serve just metadata for relationships, thus produce automatically the necessary joins. By not defining them you just put on yourself unneeded limitations (additionally to EF Core limitations/bugs). In general EF Core works better and support more things when using navigation properties instead of manual joins.

Leave a Comment

tech