Group by Weeks in LINQ to Entities

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…