Given a date column, get the preceding Sunday for grouping purposes with LINQ

Question

Given a table like this:

UserID,DateOfEntry,TimeWorked,Status
user1,2013-04-23,5,Submitted
user1,2013-04-22,7,Submitted
user1,2013-04-29,11,Submitted
user1,2013-04-24,3,Approved
user2,2013-04-22,9,Submitted

How would I go about getting this result set:

UserID,WeekStart,SumTimeWorked
user1, 2013-04-21, 12
user1, 2013-04-28, 11
user2, 2013-04-21, 9

Where the grouping is based on the userID, and the Sunday preceding the date in "DateOfEntry"?

I would prefer Lambda syntax if possible.

EDIT Here's the working query, based on Thomas' answer. This is the actual query for the actual table I'm querying, so it might not match the above example exactly.

var entries = _db.TimeEntries.GroupBy(g => new {
weekstart = g.DateOfEntry.AddDays(-(int)g.DateOfEntry.DayOfWeek),
userID = g.UserID
})
.OrderBy(c => c.Key.userID)
.Select(c => new {
userID = c.Key.userID,
weekStart = c.Key.weekstart,
Duration = c.Sum(sub => sub.Duration)
});

EDIT 2 The above Query worked in LINQPad and the actual code it is used in wasn't ready to be tested until today - unfortunately in LINQPad I was using LINQ to SQL and in code we're using LINQ to Entities. I receive this error now, if anyone has any ideas...

LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)'
method, and this method cannot be translated into a store expression.

EDIT 3 Ok, I think I've got this one figured out. I had to add two Using statements:

using System.Data.Objects;
using System.Data.Objects.SqlClient;

and then change the query to:

var entries = _db.TimeEntries.GroupBy(g => new {
weekstart = EntityFunctions.AddDays(g.DateOfEntry, -SqlFunctions.DatePart("weekday", g.DateOfEntry)),
userID = g.UserID
})
.OrderBy(c => c.Key.userID)
.Select(c => new {
userID = c.Key.userID,
weekStart = c.Key.weekstart,
Duration = c.Sum(sub => sub.Duration)
});

Sources: Linq to EntityFramework DateTime and LINQ to Entities Join on DateTime.DayOfWeek

Which type of linq is this? (to objects, to sql, to entities, ...). And can you show a preliminary effort as a start? Or at least the query that generates the first table? — Gert Arnold, Apr 24 '13 at 10:08
Fair Point, I should have included my query. I'll post the completed working query (based on Thomas' answer) in an edit. — Dakine83, Apr 25 '13 at 02:01
Nice job. You've managed to make the query even simpler by using anonymous types, that's great! I didn't know we could rely on anonymous types's **Equals** implementation. — Thomas C. G. de Vilhena, Apr 25 '13 at 13:52
Well, it worked in LINQPad, but turns out we're using LINQ to Entities in the app, not LINQ to SQL. Updated question with error I'm receiving, if you have any ideas. — Dakine83, Apr 25 '13 at 18:35
You're getting that exception because your LINQ to SQL query can't be fully translated to plain SQL (not because it is not possible, but because it is not implemented). A workaround to this issue is to fetch your **TimeEntry** rows of interest to memory and then perform a LINQ to Objects query over those rows. For instance, try replacing this **_db.TimeEntries.GroupBy** by this **_db.TimeEntries.ToArray().GroupBy** — Thomas C. G. de Vilhena, Apr 25 '13 at 18:52
Yeah, that was one of the options I saw too. That might have been a better route to go, with less overhead than importing two more libraries. I'm new to LINQ, so this was an interesting introduction to some pitfalls with it. Still impressive though. Thanks for all your help. — Dakine83, Apr 25 '13 at 18:58
Again I think you've found a better solution :). Your approach will fully translate the LINQ query to a SQL query, which will execute entirely on the SQL Server, reducing the size of the result set that will be returned to the application and saving some network bandwidth! — Thomas C. G. de Vilhena, Apr 25 '13 at 19:08

score 1 · Accepted Answer · answered Apr 23 '13 at 22:40

The linq query is quite simple actually:

var q = from s in submissions
        group s by GetKey(s) into g
        select new
        {
            UserId = g.Key.Key,
            WeekStart = g.Key.Value,
            SumTimeWorked = g.Sum(sub => sub.TimeWorked)
        };

The trick is to define an appropriate grouping method. This one uses KeyValuePairs just because I didn't want to throw in a class definition in this answer:

private KeyValuePair<string, DateTime> GetKey(Submission s)
{
    return new KeyValuePair<string, DateTime>
            (s.UserID, s.DateOfEntry.AddDays(-(int)s.DateOfEntry.DayOfWeek));
}

By the way, after running it I noticed that one of your rows has the wrong value in the SumTimeWorked column. It should be:

user1, 2013-04-21, 15

Awesome. I didn't realize I could do 's.DateOfEntry.AddDays(-(int)s.DateOfEntry.DayOfWeek))' inside the grouping statement. Thanks! — Dakine83, Apr 25 '13 at 02:05

Given a date column, get the preceding Sunday for grouping purposes with LINQ

1 Answers1