tl;dr, I have a sorted IQueryable of user scores, and I want to get the rank of an attempt (that may or may not be in the IQueryable).
I have a class Attempt that represents an attempt of my videogame.
class Attempt(int Id, string UserId, int score)
{
public int Id { get; set; } = Id;
public string UserId { get; set; } = UserId!;
public int score { get; set; } = score;
}
Let’s say I have an IQueryable<Attempt> to simulate the Entity Framework Core IQueryable on the DbContext classes. I am using PostgreSQL.
var list = new List<Attempt>();
list.Add(new Attempt(1, "Joe", 20));
list.Add(new Attempt(2, "Alan", 25));
list.Add(new Attempt(4, "Joe", 30));
list.Add(new Attempt(7, "Violet", 500));
list.Add(new Attempt(5, "Violet", 10));
list.Add(new Attempt(9, "Paul", 10));
list.Add(new Attempt(66, "Paul", 10));
var queryable = list.AsQueryable();
I want to get the theoretical rank of an arbitrary attempt whether it’s in the list or not, where “Rank” means the position in the list sorted by the score.
In case that there are ties, their ranks should all be the highest position they are tied for.
And I want to do this without converting the IQueryable to an IEnumerable so that we will not have to fetch the entire list of attempts from the DB ever.
Here are a few use examples:
// attempts to check
var attempt1 = new Attempt(4, "Joe", 30); // existent attempt - should return the existing attempts rank
var attempt3 = new Attempt(58, "Joel", 47); // non-existent attempt - should get the rank right after the attempt with the lowest score higher than itself
var attempt2 = new Attempt(57, "Jonathan", 30); // non-existent attempt with colliding score - should return the same rank as any existing attempt with the same score
How can I do this?
2
Based on this github issue currently EF Core does not support Window Functions out of the box. One of the possible options is to use linq2db.EntityFrameworkCore package which has support for them (and basically replaces the EF Core query translator). For example:
services.AddDbContext<AppContext>(b=> b.UseNpgsql(...)
.UseLinqToDB());
// ....
var array = ctx.Attempts
.ToLinqToDB()
.Select(a =>
new
{
a.UserId,
Rank = Sql.Ext.Rank().Over().OrderBy(a.Score).ToValue(),
})
// build the rest of the query
.ToArray();
Possibly something can be done with “vanilla” EF Core via interceptors or user-defined function mapping, but have not tried to use those.
Another option to look at is System.Linq.Dynamic.Core.
12
Managed to solve this. Big thanks to Guru Stron for the initial idea.
So, you can solve this on List based IQueryables with the method TakeWhile or other Window Functions, however window functions are not supported in EF Core (see issue), and Guru Stron suggested to use linq2db.EntityFrameworkCore‘s window functions.
I ended up writing these two methods:
The first is identical to Guru Stron’s answer, it creates a queryable of attempts and their ranks by utilizing Linq2db’s Window functions.
IQueryable<AttemptAndRank> GetRankedQueryable(IQueryable<Attempt> attempts)
{
return attempts.ToLinqToDB()
.Select(a =>
new AttemptAndRank(a, Sql.Ext.Rank().Over().OrderByDesc(a.score).ToValue()));
}
The second method, the getRank method, should get the rank of an attempt even if it isn’t in the queryable, this without loading the entire list into the server.
It does this by using getRankedQueryable AsSubQuery, and giving it as param the attempts queryable Union‘d with the given attempt.
long getRank(IQueryable<Attempt> attempts, Attempt attempt)
{
var score = GetRankedQueryable(attempts.Union(new List<Attempt>{ attempt })).AsSubQuery()
.FirstOrDefault(a => a.Attempt.Id == attempt.Id);
return score is not null ? score!.Rank : -1;
}