CODEWITHSUNDEEP
linq
Dakine83
Dakine83

Reputation: 707

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

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

Upvotes: 1

Views: 95

Answers (1)

Thomas C. G. de Vilhena
Thomas C. G. de Vilhena

Reputation: 14565

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

Upvotes: 1

Related Questions