Thinktecture.EntityFrameworkCore icon indicating copy to clipboard operation
Thinktecture.EntityFrameworkCore copied to clipboard

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

Open redwyre opened this issue 4 months ago • 1 comments

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()

redwyre avatar Feb 11 '24 19:02 redwyre