Adding AsSubQuery() causing other parts of the query to fail
redwyre opened this issue · 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()
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()
})