Group by Weeks in LINQ to Entities

Question

I have an application that allows users to enter time they spend working, and I'm trying to get some good reporting built for this which leverages LINQ to Entities. Because each TrackedTime has a TargetDate which is just the "Date" portion of a DateTime, it is relatively simple to group the times by user and date (I'm leaving out the "where" clauses for simplicity):

var userTimes = from t in context.TrackedTimes
                group t by new {t.User.UserName, t.TargetDate} into ut
                select new
                {
                    UserName = ut.Key.UserName,
                    TargetDate = ut.Key.TargetDate,
                    Minutes = ut.Sum(t => t.Minutes)
                };

Thanks to the DateTime.Month property, grouping by user and Month is only slightly more complicated:

var userTimes = from t in context.TrackedTimes
                group t by new {t.User.UserName, t.TargetDate.Month} into ut
                select new
                {
                    UserName = ut.Key.UserName,
                    MonthNumber = ut.Key.Month,
                    Minutes = ut.Sum(t => t.Minutes)
                };

Now comes the tricky part. Is there a reliable way to group by Week? I tried the following based on this response to a similar LINQ to SQL question:

DateTime firstDay = GetFirstDayOfFirstWeekOfYear();
var userTimes = from t in context.TrackedTimes
                group t by new {t.User.UserName, WeekNumber = (t.TargetDate - firstDay).Days / 7} into ut
                select new
                {
                    UserName = ut.Key.UserName,
                    WeekNumber = ut.Key.WeekNumber,
                    Minutes = ut.Sum(t => t.Minutes)
                };

But LINQ to Entities doesn't appear to support arithmetic operations on DateTime objects, so it doesn't know how to do (t.TargetDate - firstDay).Days / 7.

I've considered creating a View in the database that simply maps days to weeks, and then adding that View to my Entity Framework context and joining to it in my LINQ query, but that seems like a lot of work for something like this. Is there a good work-around to the arithmetic approach? Something that works with Linq to Entities, that I can simply incorporate into the LINQ statement without having to touch the database? Some way to tell Linq to Entities how to subtract one date from another?

Summary

I'd like to thank everyone for their thoughtful and creative responses. After all this back-and-forth, it's looking like the real answer to this question is "wait until .NET 4.0." I'm going to give the bounty to Noldorin for giving the most practical answer that still leverages LINQ, with special mention to Jacob Proffitt for coming up with a response that uses the Entity Framework without the need for modifications on the database side. There were other great answers, too, and if you're looking at the question for the first time, I'd strongly recommend reading through all of the ones that have been up-voted, as well as their comments. This has really been a superb example of the power of StackOverflow. Thank you all!

Answer 1

You should be able to force the query to use LINQ to Objects rather than LINQ to Entities for the grouping, using a call to the AsEnumerable extension method.

Try the following:

DateTime firstDay = GetFirstDayOfFirstWeekOfYear();
var userTimes = 
    from t in context.TrackedTimes.Where(myPredicateHere).AsEnumerable()
    group t by new {t.User.UserName, WeekNumber = (t.TargetDate - firstDay).Days / 7} into ut
    select new
    {
        UserName = ut.Key.UserName,
        WeekNumber = ut.Key.WeekNumber,
        Minutes = ut.Sum(t => t.Minutes)
    };

This would at least mean that the where clause gets executed by LINQ to Entities, but the group clause, which is too complex for Entities to handle, gets done by LINQ to Objects.

Let me know if you have any luck with that.

Update

Here's another suggestion, which might allow you to use LINQ to Entities for the whole thing.

(t.TargetDate.Days - firstDay.Days) / 7

This simply expands the operation so that only integer subtraction is performed rather than DateTime subtraction.

It is currently untested, so it may or may not work...