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-30 22:39:33Z

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, 2010 at 22:39

answered Dec 30, 2010 at 20:40

Steven K.

2,10616 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alfero Chingono Over a year ago

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 Over a year ago

With this code, I get the exception: DbArithmeticExpression arguments must have a numeric common type.

Saeed Amiri Over a year ago

TotalHours is not defined by linq2EF, It's better just have your edited answer :)

Alfero Chingono Over a year ago

I had already implemented this way, but I'll accept this for correctness.

Dave Swersky · Accepted Answer · 2010-12-30 20:41:14Z

0

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
                };

answered Dec 30, 2010 at 20:41

Dave Swersky

34.8k9 gold badges83 silver badges120 bronze badges

2 Comments

Alfero Chingono Over a year ago

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.

Dave Swersky Over a year ago

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.

ShahidAzim · Accepted Answer · 2010-12-30 20:47:36Z

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, 2010 at 20:47

ShahidAzim

1,5141 gold badge10 silver badges15 bronze badges

2 Comments

Alfero Chingono Over a year ago

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".

ShahidAzim Over a year ago

The DateDiffHour method reference can be found here: 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.

Collectives™ on Stack Overflow

Using DateDiff in Entity Framework on a SQL CE database

3 Answers 3

4 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related