Adding AsSubQuery() causing other parts of the query to fail

[redwyre]

I'm getting the error:

System.InvalidOperationException: 'The LINQ expression 'x1 => new ViewEntryPlayerDto{ 
    SteamId = x1.SteamPlayer.Id, 
    SteamName = x1.SteamPlayer.Name 
}
' could not be translated.  [...]

Here is what the debug expression looks like:

DbSet<Entry1>()
    .OrderByDescending(e => e.CattleMustered)
    .ThenBy(e => e.TimeTaken)
    .Select(e => new RankedEntry{ 
        Entry = e, 
        Rank = EF.Functions
            .RowNumber(EF.Functions
                .OrderByDescending(e.CattleMustered)
                .ThenBy(e.TimeTaken)) 
    }
    )
    .AsSubQuery()
    .OrderBy(re => re.Rank)
    .Select(x => new ViewEntryDto{ 
        Ranking = x.Rank, 
        Players = x.Entry.Players
            .Select(x1 => new ViewEntryPlayerDto{ 
                SteamId = x1.SteamPlayer.Id, 
                SteamName = x1.SteamPlayer.Name 
            }
            )
            .ToList(), 
        TimeTaken = x.Entry.TimeTaken, 
        CattleMustered = x.Entry.CattleMustered 
    }
    )

and the wrong query without it:

SELECT ROW_NUMBER() OVER(ORDER BY [e].[CattleMustered] DESC, [e].[TimeTaken]), [e].[Id], [t].[SteamId], [t].[SteamName], [t].[EntryId], [t].[PlayerNumber], [e].[TimeTaken], [e].[CattleMustered]
FROM [Entry1] AS [e]
LEFT JOIN (
    SELECT [p].[Id] AS [SteamId], [p].[Name] AS [SteamName], [e0].[EntryId], [e0].[PlayerNumber]
    FROM [EntryPlayer] AS [e0]
    INNER JOIN [Players] AS [p] ON [e0].[SteamPlayerId] = [p].[Id]
) AS [t] ON [e].[Id] = [t].[EntryId]
ORDER BY ROW_NUMBER() OVER(ORDER BY [e].[CattleMustered] DESC, [e].[TimeTaken]), [e].[Id], [t].[EntryId], [t].[PlayerNumber]

For setup I have opt.AddWindowFunctionsSupport().AddCustomQueryableMethodTranslatingExpressionVisitorFactory()

[PawelGerr]

Alas, EF Core doesn't translate everything. The root cause why you get the InvalidOperationException is the same as with following query that has nothing to do with RowNumber nor AsSubQuery:

// Entity + Distinct + Use of NavProp is currently not supported

DbSet<Entry1>()
    .Select(e => new RankedEntry { 
        Entry = e // <- "Entity" + ...
     })
    .Distinct()     // <- ... + "Distinct" + ...
    .Select(x => new ViewEntryDto{ 
        Entry = x.Entry, 
        Players = x.Entry.Players  // <- ... + "Use of NavProp"
            .Select(x1 => new ViewEntryPlayerDto{ 
                SteamId = x1.SteamPlayer.Id, 
                SteamName = x1.SteamPlayer.Name 
            })
            .ToList()
    })

If EF Core translates query above in the future then most likely AsSubQuery will work as well.

To make the issue more obvious you can try the same query without projection of the players.

DbSet<Entry1>()
    .Select(e => new RankedEntry { 
        Entry = e 
     })
    .Distinct() 
    .Select(x => new ViewEntryDto{ 
        Entry = x.Entry, 
        Players = x.Entry.Players
    })

You still doesn't get the players, but what's worse, you do not even get an exception!

---

As a workaround you can use Join/LeftJoin to get the players.
Something like

DbSet<Entry1>()
   // actually, the OrderByDescending+ThenBy shouldn't be necessary here
    .OrderByDescending(e => e.CattleMustered)
    .ThenBy(e => e.TimeTaken)
    .Select(e => new RankedEntry{ 
        Entry = e, 
        Rank = EF.Functions
            .RowNumber(EF.Functions
                .OrderByDescending(e.CattleMustered)
                .ThenBy(e.TimeTaken)) 
    })
    .AsSubQuery()
    .OrderBy(re => re.Rank)
    .LeftJoin( DbSet<Player>(), <join conditions using scalar properties only> ,
       x => new ViewEntryDto{ 
        Ranking = x.Left.Rank,  
        TimeTaken = x.Left.TimeTaken, 
        CattleMustered = x.Left.CattleMustered,
        Players = x.Right
            .Select(x1 => new ViewEntryPlayerDto{ 
                SteamId = x1.SteamPlayer.Id, 
                SteamName = x1.SteamPlayer.Name 
            })
            .ToList() 
    })