I am facing an issue with counting number of occurrences by date in C#. Should I be using Linq to filter it? Please advise. Thank you.

Date    Player ID
1/1/2001   23
1/1/2001   29
1/1/2001   24
3/1/2001   22
3/1/2001   23

My preferred output should be

Date   No. Of Players
1/1/2001     3
2/1/2001     0
3/1/2001     2

This is my current code, how can I do it within the select:

 var convertTable = dataPageTable.AsEnumerable();
 Records = new List<List<ContentOutputModel>>(convertTable.Select(dr =>
            {
                var playerId = dr.GetColumn<long>("PlayerID").ToString();
                var dateInt = dr.GetColumn<int>("Date").ToString();
                var dateStr = dateInt.Substring(6, 2)   "/"   dateInt.Substring(4, 2)   "/"   dateInt.Substring(0, 4);
                var output = new List<ContentOutputModel>(new ContentOutputModel[] {
                    new ContentOutputModel() { Text = dateStr },
                    new ContentOutputModel() { Text = playerId },
                });
                return output;
            }));

CodePudding user response：

You can achieve by Group() via System.Linq.

1. Order players by Date and get startDate and endDate.
2. Generate an array with dates from startDate to endDate.

3.1 With group to count player(s) by Date.

3.2 Left join result from (2) with the result (3.1) to get Date and Count.

using System.Linq;
using System.Collections.Generic;

List<Player> players = new List<Player>
{
    new Player{Date = new DateTime(2021, 1, 1), ID = 23}, 
    new Player{Date = new DateTime(2021, 1, 1), ID = 29}, 
    new Player{Date = new DateTime(2021, 1, 1), ID = 24}, 
    new Player{Date = new DateTime(2021, 1, 3), ID = 22}, 
    new Player{Date = new DateTime(2021, 1, 3), ID = 23}
};
        
var startDate = players.OrderBy(x => x.Date)
    .First()
    .Date;
        
var endDate = players.OrderBy(x => x.Date)
    .Last()
    .Date;
        
var dates = Enumerable.Range(0, 1   endDate.Subtract(startDate).Days)
    .Select(offset => startDate.AddDays(offset))
    .ToArray(); 
        
var result = (from a in dates
    join b in 
    (
        from p in players
        group p by p.Date into g
        select new { Date = g.Key, Count = g.Count() }
    ) on a.Date equals b.Date into ab
    from b in ab.DefaultIfEmpty()
    select new { Date = a.Date, Count = b != null ? b.Count : 0 }
); 

CodePudding user response：

var playersgroup = from e in players 
group e by Date into g 
select new { Date= g.Key, NoOfPlayers = g.Count() };