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