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

Answer 2

You can use SQL functions also in the LINQ as long as you are using SQLServer:

using System.Data.Objects.SqlClient;

var codes = (from c in _twsEntities.PromoHistory
                         where (c.UserId == userId)
                         group c by SqlFunctions.DatePart("wk", c.DateAdded) into g
                         let MaxDate = g.Max(c => SqlFunctions.DatePart("wk",c.DateAdded))
                         let Count = g.Count()
                         orderby MaxDate
                         select new { g.Key, MaxDate, Count }).ToList();

This sample was adapted from MVP Zeeshan Hirani in this thread: a MSDN

Answer 3

I had exactly this problem in my Time tracking application which needs to report tasks by week. I tried the arithmetic approach but failed to get anything reasonable working. I ended-up just adding a new column to the database with the week number in it and calculating this for each new row. Suddenly all the pain went away. I hated doing this as I am a great believer in DRY but I needed to make progress. Not the answer you want but another users point of view. I will be watching this for a decent answer.

Answer 4

Okay, this is possible, but it's a huge PITA and it bypasses the LINQ part of the Entity Framework. It took some research, but you can accomplish what you want with the SqlServer provider-specific function SqlServer.DateDiff() and "Entity SQL". This means you're back to string queries, and manually populating custom objects, but it does what you want. You might be able to refine this a bit by using a Select instead of a foreach loop and such, but I actually got this to work on a designer-generated entity set of YourEntities.

void getTimes()
{
    YourEntities context = new YourEntities();
    DateTime firstDay = GetFirstDayOfFirstWeekOfYear();
    var weeks = context.CreateQuery<System.Data.Common.DbDataRecord>(
        "SELECT t.User.UserName, SqlServer.DateDiff('DAY', @beginDate, t.TargetDate)/7 AS WeekNumber " +
        "FROM YourEntities.TrackedTimes AS t " +
        "GROUP BY SqlServer.DateDiff('DAY', @beginDate, t.TargetDate)/7, t.User.UserName", new System.Data.Objects.ObjectParameter("beginDate", firstDay));
    List<customTimes> times = new List<customTimes>();
    foreach (System.Data.Common.DbDataRecord rec in weeks)
    {
        customTimes time = new customTimes()
        {
            UserName = rec.GetString(0),
            WeekNumber = rec.GetInt32(1)
        };
        times.Add(time);
    }
}

class customTimes
{
    public string UserName{ get; set; }
    public int WeekNumber { get; set; }
}