CODEWITHSUNDEEP
c#sqllinq
Skuami
Skuami

Reputation: 1624

Top Highscore LINQ to SQL

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

Upvotes: 1

Views: 488

Answers (4)

tobias86
tobias86

Reputation: 5029

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;

Upvotes: 1

Jodrell
Jodrell

Reputation: 35726

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>

Upvotes: 0

Polity
Polity

Reputation: 15130

Something like this?

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

Upvotes: 1

Pranay Rana
Pranay Rana

Reputation: 176946

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);

Upvotes: 1

Related Questions