See dotnet/efcore#37633 for the EF/database motivation for this.

Note that we should continue the pattern in #120596 - have both overloads with a result selector (as has always been the case with all Join operators), and overloads without a result selector, which returns tuples as an easier-to-use version for the common case.