Top Highscore LINQ to SQL

Question

I have one table for my online highscore called "HighScore". This table contains following columns:

Id, int (auto value) Name, string (player name) Guid, string (player id) Score, int (score) Coins, int (player's coins) Created, datetime (create date)

What I need is the top 50 scores but grouped by the Guid. I found a LINQ expression which works almost. How do I get the MAX row with a GROUP BY in LINQ query?

In the end I need a list of my HighScore objects. With the expression above, I get a anonymous kind of list.

Edit: Actually the name of my table is "CatGameScore" but I changed it in this post.

Content of the table (guids and dates are just illustrated)

Id     Name     Guid     Score     Coins     Created
1      Hugo     123-123  150       10        <date>
2      Peter    456-456  600       19        <date>
3      Hugo     123-123  550       26        <date>

My output should be like this:

Id     Name     Guid     Score     Coins     Created
2      Peter    456-456  600       19        <date>
3      Hugo     123-123  550       26        <date>

The output must be a List. I am able to get the top 50 scores per person, but I can't create a list of my score objects.

Thanks for any hints.

Andy

can you perhaps illustrate with (say) just 4 rows, with 1 guid repeated and (say) 2 other guids, just so we can see exactly what you want as output? — Marc Gravell, Dec 15 '11 at 07:31
Why is the table called `HighScore` if it has multiple scores per player. Perhaps the table should be simply called `Score`. — Jodrell, Dec 15 '11 at 08:19

Polity · Answer 1 · 2011-12-15T07:29:46.350

1

Something like this?

context.HighScoreSet.OrderByDescending(x => x.Score)
                    .Take(50)
                    .GroupBy(x => x.Guid);

edited Dec 15 '11 at 07:29

answered Dec 15 '11 at 07:24

Polity

14,734
2
40
40

score 1 · Answer 2 · edited May 23 '17 at 11:55

1

In addition to this : How do I get the MAX row with a GROUP BY in LINQ query?

you need to use Take and Skip method that will help you to achieve your task.

something like

MyScoreCollection.OrderByDescending(x => x.Score).Take(50) .GroupBy(x => x.Guid);

edited May 23 '17 at 11:55

Community

1
1

answered Dec 15 '11 at 07:24

Pranay Rana

175,020
35
237
263

I added some comments to my first post. I need a list of my Score objects. Thanks – Skuami Dec 15 '11 at 17:50

tobias86 · Answer 3 · 2012-01-03T06:12:59.103

1

Ok something like this could do the trick

//assuming you have a List<HighScore>
var scores = new List<HighScore>();

EDIT: Get all scores from database (hit db once). Then you can work with the Tuple object without any translation to SQL

scores = (from s in context.ScoreSet //or something like this
         select s).ToList();

END EDIT

//get list of Tuples containing player Id and max score
var topScores = from s in scores
                group s.Score by s.Guid into grouping
                orderby grouping.Max() descending
                select new Tuple<string, int>(grouping.Key, grouping.Max()).Take(50);

//get matching HighScore records from List<HighScore>
var scoreRecords = from score in scores
                   let tuple = new Tuple<string, int>(score.Guid, score.Score)
                   where topScores.Contains(tuple)
                   select score;

edited Jan 03 '12 at 06:12

answered Dec 15 '11 at 08:07

tobias86

4,979
1
21
30

This code looks good to me. I get the topScores but I am not able to get the matching HighScore records from my "scores" list. I get following error message: The member 'System.Tuple`2[System.String,System.Int32].Item1' has no supported translation to SQL. – Skuami Dec 15 '11 at 18:01
@Andy : Sorry for only getting back to this now, been away from my PC the last couple of weeks. It looks like you're using a LINQ-to-SQL implementation, correct? If that is the case, then yes, there is no obvious translation of a `Tuple` object to SQL. A possible workaround could be to get everything from the table before you start filtering the high scores etc. I'll post an edit to my answer to illustrate. – tobias86 Jan 03 '12 at 06:07

Jodrell · Answer 4 · 2011-12-15T09:43:17.867

Here is my answer, I've called the HighScore table Scores because that seems more correct to me.

var highScorePerPlayer =
    scores.GroupBy(
        s => s.Guid,
        s => s.Score,
        (playerID, scores) => 
            new KeyValuePair<string,int> (playerID, scores.Max()));

var top50HighScores = highScorePerPlayer.OrderByDescending(hs => hs.Value)
    .Take(50);

From reading the question I think you want a distinct high score from each player, so each player will appear only once in the list. If this is not the case you should reverse the operations, like the other answers. The result will be an IEnumerable<KeyValuePair<string, int>

Top Highscore LINQ to SQL

4 Answers4