C# group List by date field - Weekly

Question

I have list of next Entity "News"

class News
{
    public virtual int Id { get; protected set; } 

    public virtual string Content { get; protected set; }

    public virtual DateTime Date { get; set; }
 }

Now I want to get count of them grouped by week.

This is the method I have:

public Dictionary<DateTime, int> GetCountByWeek(DateTime fromDate)
{
     var vals =
            from l in session.Query<News>().ToList()
            where l.Date > fromDate
            group l by (l.Date.DayOfYear - FirstMonday(fromDate.Year)) / 7
                into gr
                select new { Key = new DateTime(gr.Min(m => m.Date.Year), gr.Min(m => m.Date.Month), gr.Min(m => m.Date.Last(DayOfWeek.Monday).Day)), Value = gr.Count() };
        return vals.ToDictionary(l => l.Key, l => l.Value);
}

Function to get first Monday with FromDate:

public static int FirstMonday(int year)
{
        int day = 0;

        while ((new DateTime(year, 01, ++day)).DayOfWeek != System.DayOfWeek.Monday) ;

        return day;
}

This should return me Dictionary with date of the first Day of the week and count of News for that week like:

07.03.2016 => 15,
14.03.2016 => 7,
21.03.2016 => 8,

etc...

This is not working well because it returns duplicate date keys like:

07.03.2016 => 15,
07.03.2016 => 7,
14.03.2016 => 8,

But it should be like:

07.03.2016 => 15,
14.03.2016 => 7,
21.03.2016 => 8,

I am not so good with these things, so if someone can tell me what I am doing wrong here.

score 2 · Accepted Answer · answered Mar 23 '16 at 18:46

2

I'd just use the date as the group key:

public Dictionary<DateTime, int> GetCountByWeek(DateTime fromDate)
{
     var vals =
            from l in session.Query<News>().ToList()
            where l.Date > fromDate
            group l by MyMonday(l.Date.Date) // Use Date.Date to ignore any time values
                into gr
                select new { Key = gr.Key, Value = gr.Count() };
        return vals.ToDictionary(l => l.Key, l => l.Value);
}

And the MyMonday method:

public static DateTime MyMonday(DateTime date)
{
    var myMon = date;
    while (myMon.DayOfWeek != System.DayOfWeek.Monday) {
        myMon = myMon.AddDays(-1);
    }
    return myMon;
}

answered Mar 23 '16 at 18:46

Sean

8,407
3
31
33

this seams working fine. But I still don't understand using "Date.Date". If you please can explain to me – carpics Mar 23 '16 at 23:33
The DateTime.Date instance method yields the date with a time of 00:00:00 (12 AM). So the following evaluates to true: DateTime("1/2/2016 9:00:00").Date == DateTime("1/2/2016 15:00:00").Date. That's what you want for the grouping. – Sean Mar 24 '16 at 01:06

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

I've borrowed a nice extension method from here and then used it like this:

public Dictionary<DateTime, int> GetCountByWeek(DateTime fromDate)
{
    Dictionary<string, int> dict = session.Query<News>().ToList()
        .Where(n => n.Date > fromDate)
        .GroupBy(n => n.StartOfWeek())
        .ToDictionary(g => g.Key, g => g.Count());


    return dict;
}

Extension method

public static class NewsExtensions
{
    public static DateTime StartOfWeek(this News news)
    {
        int diff = news.Date.DayOfWeek - DayOfWeek.Monday;
        if (diff < 0)
        {
            diff += 7;
        }
        return news.Date.AddDays(-1 * diff).Date;
    }
}

I haven't tested it, but please give it a try.

C# group List by date field - Weekly

2 Answers2