Reports

Managed to solve this with a raw query, thanks Microsoft copilot...

IQueryable<Attempt> GetBestAttempts() =>
   Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.FromSqlRaw(db.Attempt, @"
SELECT a.*
FROM ""Attempt"" a
INNER JOIN (
    SELECT ""UserId"", MAX(""score"") AS ""MaxScore""
    FROM ""Attempt""
    GROUP BY ""UserId""
) maxAttempts ON a.""UserId"" = maxAttempts.""UserId"" AND a.""score"" = maxAttempts.""MaxScore""
WHERE a.""Id"" = (
    SELECT MIN(innerA.""Id"")
    FROM ""Attempt"" innerA
    WHERE innerA.""UserId"" = a.""UserId"" AND innerA.""score"" = a.""score""
)
ORDER BY a.""score"" DESC, a.""UserId"";
");

As much as I hate raw SQL queries, this will do for now.

(I called the extension method directly because I also use Linq2Db in my project and want to avoid name conflicts)

79150697