Count occurrences of an event by date with C#

Question

I am facing an issue with counting the number of occurrences by date in C#. Should I use 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;
            }));

score 3 · Answer 1 · answered Jan 05 '22 at 07:42

Here's the cleanest that I could come up with:

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 first = players.Min(p => p.Date);
var last = players.Max(p => p.Date);

var days = last.Subtract(first).Days + 1;

var lookup = players.ToLookup(p => p.Date);

var output =
    from n in Enumerable.Range(0, days)
    let Date = first.AddDays(n)
    select new
    {
        Date,
        Count = lookup[Date].Count(),
    };

That gives me:

score 2 · Answer 2 · answered Jan 05 '22 at 05:12

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

Order players by Date and get startDate and endDate.
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 }
);

Sample program

Output

Date: 1/1/2021, Count: 3

Date: 2/1/2021, Count: 0

Date: 3/1/2021, Count: 2

score 0 · Answer 3 · answered Jan 05 '22 at 05:12

0

You can use linq to do this as long as it can enumerate through a list or some other IEnumerable. Try this:

var playersSorted = yourlist.GroupBy(x => x.Date)
            .Where(g => g.Any())
            .Select(y => new {Date = y.Key, Count = y.Count()}).ToList();

answered Jan 05 '22 at 05:12

mattybmaker

25
5

It will return a list of anonymous objects, but it should do the job just fine – mattybmaker Jan 05 '22 at 05:14
1

There is no need for `.Where(g => g.Any())` after a `GroupBy`. – Enigmativity Jan 05 '22 at 07:33

score -1 · Answer 4 · edited Jan 05 '22 at 07:34

-1

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

edited Jan 05 '22 at 07:34

Enigmativity

113,464
11
89
172

answered Jan 05 '22 at 04:49

user2626026

21
3
8

I've edited my comment to be more precise. Thanks so much for helping! – curiouscat Jan 05 '22 at 05:00
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 05 '22 at 06:34
Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney Jan 06 '22 at 01:13

Count occurrences of an event by date with C#

4 Answers4