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…

Accepted Answer

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...

Group by Weeks in LINQ to Entities

Solution

Problem