Query (or LINQ in Entity Framework) for getting user's rank

Question

I have a SQL Server database with a table named Users.

This table has 2 columns: XP (int32) and Name (nvarchar).

I need a SQL query or Linq (this is better because I'm using Entity Framework in C#) that gets the Name and ranks everyone based on XP and display the rank of this name + the rank of the previous 1 people + the rank of the next 1 people.

Something like this :

Answer 1

In EF terms it seems like it could just be:

var d = db.Users
  .OrderByDescending(u => u.Xp)
  .AsEnumerable()
  .Select((u, i) => new { Rank=i+1, User = u })
  .ToDictionary(at => at.Rank);

You list all your users out of the DB in order of XP, and you use the overload of Select((user,index)=>...) to assign the rank (the index + 1) and for convenience let's put them in a dictionary indexed by rank

Now you have a dictionary you can keep and repeatedly look up in where, for any given user, you can get the surrounding users by the rank of the user you already know:

var user = d.Values.FirstOrDefault(u => u.User.Name == "user6");
var prev = d[user.Rank - 1];
var next = d[user.Rank + 1];

You need some code to prevent a KeyNotFoundException if you find the top or bottom ranked user because if you find the user with Rank 1 by name, you cannot retrieve the user with Rank 0 (they don't exist). You didn't say what to do in this situation in your original post, you can use ContainsKey to test if the dictionary contains a particular rank, or you can use GetValueOrDefault, or TryGetValue to attempt to get a user - these methods don't crash if the user isn't found but you will need to decide what to do if they aren't

Answer 2

Sure. Try this

SELECT
    XP,
    name,
    RANK() OVER (ORDER BY XP DESC) AS Ranking
FROM UserExperience;