title: Group by Weeks in LINQ to Entities
body: <p>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 <code>TrackedTime</code> has a <code>TargetDate</code> which is just the "Date" portion of a <code>DateTime</code>, it is relatively simple to group the times by user and date (I'm leaving out the "where" clauses for simplicity):</p>
  
  <pre><code>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 =&gt; t.Minutes)
                  };
  </code></pre>
  
  <p>Thanks to the <code>DateTime.Month</code> property, grouping by user and Month is only slightly more complicated:</p>
  
  <pre><code>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 =&gt; t.Minutes)
                  };
  </code></pre>
  
  <p>Now comes the tricky part.  Is there a reliable way to group by Week?  I tried the following based on <a href="https://stackoverflow.com/questions/687968/need-some-groupby-linq2sql-help-please/688071#688071">this response</a> to a similar LINQ to SQL question:</p>
  
  <pre><code>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 =&gt; t.Minutes)
                  };
  </code></pre>
  
  <p>But LINQ to Entities doesn't appear to support arithmetic operations on DateTime objects, so it doesn't know how to do <code>(t.TargetDate - firstDay).Days / 7</code>.</p>
  
  <p>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?</p>
  
  <h2>Summary</h2>
  
  <p>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!</p>