Entity Framework Pitfalls: Date/Time Operations
When using Entity Framework, you cannot perform date/time operations as you would normally do in .NET/LINQ to Objects. For instance, the following query throws an exception:
1: ctx.Projects.Select(x => new { ElapsedTime = DateTime.Now - x.Start }).ToList();
Instead, you need to use the static methods in SqlFunctions, like DateDiff:
1: ctx.Projects.Select(x => new { ElapsedTime = SqlFunctions.DateDiff("HOUR", x.Start, DateTime.Now) }).ToList();
For a full description of DateDiff’s first parameter, refer to http://msdn.microsoft.com/en-us/library/ms189794.aspx.
If you want to achieve the same result, that is, return a TimeSpan, you need to use LINQ to Objects at the end:
1: ctx.Projects.Select(x => new { ElapsedTime = SqlFunctions.DateDiff("HOUR", x.Start, DateTime.Now) }).ToList().Select(x => new { ElapsedTime = TimeSpan.FromHours((Double)x.ElapsedTime) }).ToList();