Another problem with LINQ Pivot is converting SQL Script to LINQ

Question

There are several questions about SO already regarding LINQ pivots, and although some of them describe my exact problem, I cannot successfully translate them into a working solution. I feel this is mainly due to connecting to my tables.

So in the interests of all LINQ addicts who love the problem, here's another mystery for you. Please help me (and earn me some reputation points and a lot of respect) by translating the following SQL-stored proc script into LINQ:

ALTER PROCEDURE [dbo].[GetTimesheetForWeekById] @timesheetid int, @begindate VarChar(20), @enddate VarChar(20) AS BEGIN SELECT T.TaskName, SUM( case DATEPART(weekday, TE.StartTime) WHEN 1 THEN DATEDIFF(minute, TE.StartTime, TE.EndTime) ELSE 0 END ) AS Sunday, SUM( case DATEPART(weekday, TE.StartTime) when 2 THEN DATEDIFF(minute, TE.StartTime, TE.EndTime) ELSE 0 END ) AS Monday, SUM( case DATEPART(weekday, TE.StartTime) when 3 THEN DATEDIFF(minute, TE.StartTime, TE.EndTime) ELSE 0 END ) AS Tuesday, SUM( case DATEPART(weekday, TE.StartTime) when 4 THEN DATEDIFF(minute, TE.StartTime, TE.EndTime) ELSE 0 END ) AS Wednesday, SUM( case DATEPART(weekday, TE.StartTime) when 5 THEN DATEDIFF(minute, TE.StartTime, TE.EndTime) ELSE 0 END ) AS Thursday, SUM( case DATEPART(weekday, TE.StartTime) when 6 THEN DATEDIFF(minute, TE.StartTime, TE.EndTime) ELSE 0 END ) AS Friday, SUM( case DATEPART(weekday, TE.StartTime) when 6 THEN DATEDIFF(minute, TE.StartTime, TE.EndTime) ELSE 0 END ) AS Saturday FROM Tasks T INNER JOIN TimeEntries TE on T.TaskID = TE.TaskID WHERE TE.StartTime BETWEEN (CONVERT(datetime, @begindate, 103)) AND (CONVERT(datetime, @enddate, 103)) AND TE.TimesheetID = @timesheetid GROUP BY T.TaskName END 

Answer 1

Well, assuming the foreign key has an object representation, something like:

  int timesheetId = ... DateTime start = ..., end = ... var qry = from timeEntry in ctx.TimeEntries let date = timeEntry.StartTime.Date where timeEntry.TimesheetId == timesheetId && date >= start && date <= end group timeEntry by timeEntry.Task.TaskName into grp select new { TaskName = grp.Key, Monday = grp.Where(x => x.StartTime.DayOfWeek == DayOfWeek.Monday).Count(), Tuesday = grp.Where(x => x.StartTime.DayOfWeek == DayOfWeek.Tuesday).Count(), Wednesday = grp.Where(x => x.StartTime.DayOfWeek == DayOfWeek.Wednesday).Count(), Thursday = grp.Where(x => x.StartTime.DayOfWeek == DayOfWeek.Thursday).Count(), Friday = grp.Where(x => x.StartTime.DayOfWeek == DayOfWeek.Friday).Count() }; 

Unfortunately, some features depend on the SQL provider - that is, which functions (e.g. DayOfWeek, etc.) can be successfully mapped as TSQL. Let me know if you have problems ...

Answer 2

How is it (Marc's change):

 var qry = from timeEntry in timeEntries let date = timeEntry.StartTime.Date where timeEntry.TimesheetId == timesheetId && date >= start && date <= end group timeEntry by timeEntry.Task.TaskName into grp select new { TaskName = grp.Key, Monday = grp.Where(x => x.StartTime.DayOfWeek == DayOfWeek.Monday) .Sum(x=>x.EndTime.Subtract(x.StartTime).TotalMinutes), Tuesday = grp.Where(x => x.StartTime.DayOfWeek == DayOfWeek.Tuesday) .Sum(x => x.EndTime.Subtract(x.StartTime).TotalMinutes), Wednesday = grp.Where(x => x.StartTime.DayOfWeek == DayOfWeek.Wednesday) .Sum(x => x.EndTime.Subtract(x.StartTime).TotalMinutes), Thursday = grp.Where(x => x.StartTime.DayOfWeek == DayOfWeek.Thursday) .Sum(x => x.EndTime.Subtract(x.StartTime).TotalMinutes), Friday = grp.Where(x => x.StartTime.DayOfWeek == DayOfWeek.Friday) .Sum(x => x.EndTime.Subtract(x.StartTime).TotalMinutes), };