Using DateDiff in Entity Framework on a SQL CE database

Question

I have a method which should return a list of anonymous objects with a calculated column like this:

        var tomorrow = DateTime.Today.AddDays(1);
        return from t in this.Events
                where (t.StartTime >= DateTime.Today && t.StartTime < tomorrow && t.EndTime.HasValue)
                select new
                {
                    Client = t.Activity.Project.Customer.Name,
                    Project = t.Activity.Project.Name,
                    Task = t.Activity.Task.Name,
                    Rate = t.Activity.Rate.Name,
                    StartTime = t.StartTime,
                    EndTime = t.EndTime.Value,
                    Hours = (System.Data.Objects.SqlClient.SqlFunctions.DateDiff("m", t.StartTime, t.EndTime.Value) / 60),
                    Description = t.Activity.Description
                };

Unfortunately I get the following error from the DateDiff function:

The specified method 'System.Nullable1[System.Int32] DateDiff(System.String, System.Nullable1[System.DateTime], System.Nullable`1[System.DateTime])' on the type 'System.Data.Objects.SqlClient.SqlFunctions' cannot be translated into a LINQ to Entities store expression.

Any ideas what I could have done wrong here?

EDIT: I also tried the EntityFunctions class mentioned here, but that did not work as well.

Minutes = EntityFunctions.DiffMinutes(t.EndTime, t.StartTime),

Steven K. · Accepted Answer · 2010-12-30T22:39:33.223

4

[Edit]

Hours = (System.Data.Objects.SqlClient.SqlFunctions.DateDiff("mi", t.StartTime, t.EndTime.Value) / 60)

is not supported SQL CE.

Hours = ((TimeSpan)(t.EndTime.Value - t.StartTime)).TotalHours

Throws an DbArithmeticExpression Exception

So, I think you'll have to do it in two steps. Grab the data you need, then calculate the time difference in memory.

var events = (from t in context.Events
    where (t.StartTime >= DateTime.Today && t.StartTime < tomorrow && t.EndTime.HasValue)
    select t).ToArray();

return from t in events
    select new
    {
         ...
         Hours = (t.EndTime.Value - t.StartTime).TotalHours
    };

edited Dec 30 '10 at 22:39

answered Dec 30 '10 at 20:40

Steven K.

2,097
16
15

This is what I started off with, and it could not be translated. So I searched and the recommended approach was to use SqlFunctions. – Alfero Chingono Dec 30 '10 at 20:45
With this code, I get the exception: **DbArithmeticExpression arguments must have a numeric common type.** – Alfero Chingono Dec 30 '10 at 21:35
TotalHours is not defined by linq2EF, It's better just have your edited answer :) – Saeed Amiri Dec 30 '10 at 22:20
I had already implemented this way, but I'll accept this for correctness. – Alfero Chingono Dec 31 '10 at 04:44

score 0 · Answer 2 · answered Dec 30 '10 at 20:41

You're asking the EF provider to handle the DATEDIFF in SQL, which I don't think is possible in any version of SQL. Use native DateTime functions:

var tomorrow = DateTime.Today.AddDays(1);
        return from t in this.Events
                where (t.StartTime >= DateTime.Today && t.StartTime < tomorrow && t.EndTime.HasValue)
                select new
                {
                    Client = t.Activity.Project.Customer.Name,
                    Project = t.Activity.Project.Name,
                    Task = t.Activity.Task.Name,
                    Rate = t.Activity.Rate.Name,
                    StartTime = t.StartTime,
                    EndTime = t.EndTime.Value,
                    Hours = t.EndTime.Value(t.StartTime.Subtract).Hours,
                    Description = t.Activity.Description
                };

Hi Dave, Thanks for your response. I tried `Hours = t.EndTime.Value.Subtract(t.StartTime)` and I got the error message **LINQ to Entities does not recognize the method 'System.TimeSpan Subtract(System.DateTime)' method, and this method cannot be translated into a store expression.** — Alfero Chingono, Dec 30 '10 at 20:49
EF is still trying to translate into T-SQL. You will probably have to get the resultset without the datediff and then project it into a new one. — Dave Swersky, Dec 30 '10 at 23:28

score 0 · Answer 3 · answered Dec 30 '10 at 20:47

0

Two possibilities, remove Value from EndTime:

Hours = (System.Data.Objects.SqlClient.SqlFunctions.DateDiff("m", t.StartTime, t.EndTime) / 60)

Or use DateDiffHour:

Hours = (System.Data.Objects.SqlClient.SqlFunctions.DateDiffHour(t.StartTime, t.EndTime)

answered Dec 30 '10 at 20:47

ShahidAzim

1,446
1
10
15

Hi Shahid! Thanks for your response. Unfortunately the first option gives me the same error message. And the second, well, there is no such method "DateDiffHour". – Alfero Chingono Dec 30 '10 at 20:54
The DateDiffHour method reference can be found here: http://msdn.microsoft.com/en-us/library/bb495790.aspx But it may not supported by CE framework. Otherwise I would prefer to handle dates within C# code, instead of SQL specific, as mentioned by @Steven Kimpe. – ShahidAzim Dec 30 '10 at 21:10

Using DateDiff in Entity Framework on a SQL CE database

3 Answers3