LINQ query with grouping and ranking

Question

I have a sql table called predictions with the data below

Week Player Points 201101 Mark 7 201101 Mark 7 201101 Pete 7 201101 Pete 3 201101 Mark 5 201102 Mark 2 201102 Mark 3 201102 Pete 7 201102 Pete 5 201102 Mark 5 201103 Mark 7 201103 Mark 7 201103 Pete 7 201103 Pete 3 201103 Mark 5 

Each row in the table represents a football match, so several per week

As a result i need

 Player Count of Weekly wins Mark 2 Pete 1 

Thus, Mark had the most points for the week 2011011 and 201103, Pete had more points for the week 201102

Getting the total points per player per week is easy. But I can’t understand how to get the most out of this weekly group and get the result that I need.

I have everything in the sql query using the RANK function, and then selecting all the players with a rank of 1. This is good, because if two players have the same score in a week, they both are calculated correctly. But I want the LINQ version because it is cool and suitable for my website rework!

Any help would be appreciated.

OK. I have so far that summarizes the data for each player for each week. What I now need to do is select the top entry for each week and count it against the player.

 (from p in FootballPredictions where p.FootballMatches.WeekNum <= 201101 && p.Points != null group p by new { p.FootballMatches.WeekNum, p.Player } into g orderby g.Key.WeekNum, g.Sum(p => p.Points) descending select new { WeekNum = g.Key.WeekNum, Player = g.Key.Player, Points = g.Sum(p => p.Points), }) 

Provision

 WeekNum Player Points 201033 ranteld 26 <--- 1 point 201033 nicolap 25 201033 Mark 25 201033 1969 cup winners 25 201033 footysi 24 201033 Brendan 22 201033 monty 22 201033 Sandra Phillips 21 201033 SPB 20 201033 John Poulton 20 201033 RobBrown 19 201033 Steve Gardner 17 201033 Nick 16 201033 MikeSpeke 15 201034 Sandra Phillips 32 <--- 1 point 201034 Steve Gardner 27 201034 ranteld 25 201034 John Poulton 23 201034 footysi 23 201034 Mark 17 201034 nicolap 13 201034 Brendan 13 201035 Brendan 34 <--- 1 point 201035 Sandra Phillips 34 <--- 1 point 201035 nicolap 31 201035 1969 cup winners 25 201035 MikeSpeke 24 201035 Steve Gardner 22 201035 Mark 20 201035 ranteld 20 201035 Football Freddie 16 

So the real answer from this table

 Player Wins Sandra Philips 2 Brendan 1 ranteld 1 

Hope to clarify

Answer 1

It was somewhat vague to see that your request did not match the data. Therefore, instead, it will be based only on data. The query must contain valid SQL, so you do not have to use LINQ to Objects. You can adapt it to your tables with slight modifications.

 var query = from pred in Predictions group pred.Points by pred.WeekNum into week join pred in Predictions on new { WeekNum = week.Key, Points = week.Max() } equals new { pred.WeekNum, pred.Points } group 1 by pred.Player into player let Wins = player.Count() orderby Wins descending, player.Key select new { Player = player.Key, Wins, }; 

Answer 2

Try:

 p.Points.Any() 

Instead:

 p.Points != null 

Answer 3

This code, apparently, you need:

  var result = this.DataList .GroupBy(data => data.Week) .Select(data=> { return data.GroupBy(item => item.Name) .Select(item => new { Name = item.Key, SumPoints = item.Sum(v => v.Points) }) .OrderBy(item => item.SumPoints) .FirstOrDefault(); }) .GroupBy(_=>_.Name) .ToDictionary(_=>_.Key, _=>_.Count()); 

Answer 4

my pretty long

 var weeklyTopScore = from eachMatch in FootballPredictions group eachMatch by eachMatch.week into weekly select new {week = weekly.Key, topScore = weekly.Max(match => match.points)}; var playerWins = from eachResult in weeklyTopScore join entry in FootballPredictions on eachResult.week equals entry.week where eachResult.topScore == entry.points group entry by entry.player into winner select new { player = winner.Key, wins = winner.Count() }; var result = from entry in playerWins group entry by entry.wins into summary select new { player = summary.Select(data => data.player).Aggregate((cur, nex) => cur + ", " + nex), wins = summary.Key}; 

Answer 5

Just pass the data for the next function (note that the code is in C #) // dt should contain columns, but not rank the columns

  public static DataTable GetRankedDatatable(DataTable dt) { var rankedDt = (from row in dt.AsEnumerable() orderby row.Field<string>("points") select row).CopyToDataTable(); rankedDt.Columns.Add("rank"); int rank = 0; for (int i = 0; i < rankedDt.Rows.Count - 1; i++) { rankedDt.Rows[i]["rank"] = rank; if (rankedDt.Rows[i]["points"].ToString() != rankedDt.Rows[i + 1]["points"].ToString()) { rank++; } } rankedDt.Rows[rankedDt.Rows.Count - 1]["rank"] = rank; return rankedDt; }