LINQ - Group by calculated field

Question

Say I have some class like this:

public class MyClass 
{
    public DateTime Date { get; set; }
    public string Code { get; set; }
}

And some values like:

| Date          | Code  |
|------------   |------ |
| 03/04/2017    | 1234  |
| 31/03/2017    | 1234  |
| 29/03/2017    | 1234  |
| 29/03/2017    | 4321  |
| 25/03/2017    | 4321  |
| ...           | ...   |

I want to group these by the Code field AND also group by the Date however I want the Date to be grouped by a range. The calculation on this will find the date at the start of the week (Monday of that week) and also the end of the week (Friday of that week) and then give me some results like:

| 03/04/2017    | 1234  | < week beginning 03/04 and code=1234

| 31/03/2017    | 1234  | < week beginning 27/03 and code=1234
| 29/03/2017    | 1234  |

| 29/03/2017    | 4321  | < week beginning 27/03 and code=4321

| 25/03/2017    | 4321  | < week beginning 20/03 and code=4321

I've tried doing a range but I think what I'm doing is pretty useless (StartOfWeek is an extension method from here):

data.Where(d => d.Date >= d.Date.StartOfWeek(DayOfWeek.Monday) && d.Date <= d.Date.StartOfWeek(DayOfWeek.Monday).AddDays(6));

Note: I'm using Entity Framework for my actual project but when I'm doing this I am fetching all the data from the MyClass table and I want to do all the above to filter and group the data appropriately.

Is this in LINQ to Objects, LINQ to SQL, EF, something else? — Jon Skeet, Apr 03 '17 at 10:29
@JonSkeet I'm using EF for my actual project, this is just a rough example of the problem I'm facing atm. My underlying data is all in SQL and being accessed through EF (UnitOfWork). — sham, Apr 03 '17 at 10:30
Right, but that detail really matters - a solution that works in LINQ to Objects may well not work in EF, for example. — Jon Skeet, Apr 03 '17 at 10:31
@JonSkeet Right I see what you're saying, but if I get all relevant data I need from a Repository into a variable, is it still important to care about EF? As the data-set I need is now grabbed. — sham, Apr 03 '17 at 11:06
@Toby: If you've fetched everything into memory, you can then use LINQ to Objects, yes. But you should explicitly say all of this *in the question*. — Jon Skeet, Apr 03 '17 at 11:14
@JonSkeet I've updated the question to add some more clarity. — sham, Apr 03 '17 at 11:17

score 0 · Answer 1 · answered Apr 03 '17 at 10:46

The code below uses Sunday as the 1st day of the week. To get the week you need to divide the Day of the Year by 7. If the 1st day of the year is Monday you need to add 1 to Day of the Year So that the days of the week are Sun = 0, Monday = 1, .... Since Jan 1 is day 1 (not 0) so the divide method works correctly.

            DataTable dt = new DataTable();
            dt.Columns.Add("Date",typeof(DateTime));
            dt.Columns.Add("Code",typeof(int));

            dt.Rows.Add(new object[] { DateTime.Parse("04/03/2017"), 1234});
            dt.Rows.Add(new object[] { DateTime.Parse("03/31/2017"), 1234});
            dt.Rows.Add(new object[] { DateTime.Parse("03/29/2017"), 1234});
            dt.Rows.Add(new object[] { DateTime.Parse("03/29/2017"), 4321});
            dt.Rows.Add(new object[] { DateTime.Parse("03/25/2017"), 4321});

            var groups = dt.AsEnumerable().GroupBy(x => (int)(x.Field<DateTime>("Date").DayOfYear + (int)new DateTime(x.Field<DateTime>("Date").Year, 1, 1).DayOfWeek)/7).ToList();

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

I have tried to use week number, so first group by code and then weekOfYear + Year.

List<MyClass> date = new List<MyClass>
{
    new MyClass { Date = new DateTime(2017,03,04), Code = "1234"},
    new MyClass { Date = new DateTime(2017,03,05), Code = "1234"},
    new MyClass { Date = new DateTime(2017,03,06), Code = "1234"},
    new MyClass { Date = new DateTime(2017,04,04), Code = "1234"},
    new MyClass { Date = new DateTime(2017,04,05), Code = "1234"},
    new MyClass { Date = new DateTime(2017,04,06), Code = "1234"},
    new MyClass { Date = new DateTime(2017,03,06), Code = "12345"},
    new MyClass { Date = new DateTime(2017,04,04), Code = "12345"},
    new MyClass { Date = new DateTime(2017,04,05), Code = "12345"},
    new MyClass { Date = new DateTime(2017,04,06), Code = "12345"}
            };

var groupbydata = date.GroupBy(x => x.Code).ToDictionary(x => x.Key, x => x.GroupBy(y => CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(y.Date, CalendarWeekRule.FirstFullWeek, DayOfWeek.Monday).ToString() + y.Date.Year.ToString()));

Update:

I have changed the

x => x.GroupBy(y => CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(y.Date, CalendarWeekRule.FirstFullWeek, DayOfWeek.Monday) + y.Date.Year));

to

x => x.GroupBy(y => CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(y.Date, CalendarWeekRule.FirstFullWeek, DayOfWeek.Monday).ToString() + y.Date.Year.ToString()));

You will get Dictionary<string, IEnumerable<IGrouping<string, MyClass>>> the Key of Dictionary is Code and the Value of the Dictionary is grouped data by Week number + Year

Also try to print it out

    private static void Print(Dictionary<string, IEnumerable<IGrouping<string, MyClass>>> groupbydata)
    {
        foreach (var d1 in groupbydata)
        {
            Debug.WriteLine("Code : " + d1.Key);

            foreach (var d2 in d1.Value)
            {
                Debug.WriteLine("Week number + Year: " + d2.Key);
                foreach (var d3 in d2)
                {
                    Debug.WriteLine("Date : " + d3.Date);
                }
            }
        }
    }

And the result is

Code : 1234

Week number + Year: 92017

Date : 3/4/2017 12:00:00 AM

Date : 3/5/2017 12:00:00 AM

Week number + Year: 102017

Date : 3/6/2017 12:00:00 AM

Week number + Year: 142017

Date : 4/4/2017 12:00:00 AM

Date : 4/5/2017 12:00:00 AM

Date : 4/6/2017 12:00:00 AM

Code : 12345

Week number + Year: 102017

Date : 3/6/2017 12:00:00 AM

Week number + Year: 142017

Date : 4/4/2017 12:00:00 AM

Date : 4/5/2017 12:00:00 AM

Date : 4/6/2017 12:00:00 AM

What is the `Key` here? How can I iterate the groups that are captured in the query since there are 2 groups? Can you add an example to how I can do this? (Sorry I'm not very experienced with C#/LINQ) — sham, Apr 03 '17 at 11:08

LINQ - Group by calculated field

2 Answers2