CODEWITHSUNDEEP
c#asp.netlistlinqgroup-by
leavinus
leavinus

Reputation: 51

C# - LINQ - Group collection by date range

Let's say I have the following collection of items:

ID: 1, Date: 10/01/2020 08:00
ID: 2, Date: 10/01/2020 12:00
ID: 3, Date: 10/01/2020 18:00
ID: 4, Date: 11/01/2020 06:00
ID: 5, Date: 11/01/2020 08:00

ID: 6, Date: 11/01/2020 08:01
ID: 7, Date: 11/01/2020 12:00
ID: 8, Date: 12/01/2020 01:00
ID: 9, Date: 12/01/2020 07:00

ID: 10, Date: 12/01/2020 12:00
ID: 11, Date: 12/01/2020 14:00
ID: 12, Date: 12/01/2020 16:00

Based on the above collection i want to create 3 groups, which contains records grouped by time interval - from an hour 08:00 till 07:59 next day:

XX.01.2020 08:00 - XX(+1).01.2020 07:59

The output grouped collection should look like this

**Group 1 (10.01.2020 08:00 - 11.01.2020 07:59):**
   ID: 1, Date: 10/01/2020 08:00
   ID: 2, Date: 10/01/2020 12:00
   ID: 3, Date: 10/01/2020 18:00
   ID: 4, Date: 11/01/2020 06:00
   ID: 5, Date: 11/01/2020 07:59
**Group 2 (11.01.2020 08:00 - 12.01.2020 07:59):**
   ID: 6, Date: 11/01/2020 08:00
   ID: 7, Date: 11/01/2020 12:00
   ID: 8, Date: 12/01/2020 01:00
   ID: 9, Date: 12/01/2020 07:00
**Group 3 (12.01.2020 08:00 - 13.01.2020 07:59):**
   ID: 10, Date: 12/01/2020 12:00
   ID: 11, Date: 12/01/2020 14:00
   ID: 12, Date: 12/01/2020 16:00

Is this possible using Linq? Thank you in advance

Upvotes: 0

Views: 646

Answers (2)

Leaky
Leaky

Reputation: 3636

You didn't specify if you want this to run in database.

But I think one really easy way to go could be substracting 8 hours to make it a whole day, which you can use as the basis of grouping:

var result = list.GroupBy(x => x.Date.AddHours(-8).Date);

And if you want to get a grouping with proper 'from' and 'to' dates, like how you seemed to specify, you can slap this projection to the end:

    .Select(g => new {
        From = g.Key.AddHours(8),
        To = g.Key.AddHours(8).AddDays(1),
        Values = g.AsEnumerable()
    });

This might work for normal Linq on in-memory collections. Won't be translated to SQL though if you run it on IQueryable (unless I'm obtuse).

It's okay if you accept the other answer, if that looks better to you. :)

Upvotes: 0

Katebrich
Katebrich

Reputation: 88

I don't know what collection you have, but let's say you have Dictionary<int, DateTime>.

Then you could groupby like this, 'rounding' to the current day if it's after 8, or to the previous day if it is before 8:

    var groups = dictionary.GroupBy(x =>
        {
            var dt = x.Value;
            if (dt.Hour >= 8)
                return dt.Date;
            else
                return dt.Subtract(TimeSpan.FromHours(24)).Date;
        });

Upvotes: 1

Related Questions