Calculating time difference on datetime object in Entity Framework giving error

Question

I am trying to calculate time difference between 2 date and want to get day name of my date.

For Ex.: 4/7/2016

Day: Monday

Here is my class:

public class Attendance
{
      public int Id { get; set; }
      public Nullable<System.DateTime> StartDateTime { get; set; }
      public Nullable<System.DateTime> EndDateTime { get; set; }
}

When I am trying to do this:

 var query = (from t in context.Attendance
              select new 
              {
                 timeDiff=t.EndDateTime.Value.Subtract(t.StartDateTime.Value).TotalHours,
                 Day=System.StartDateTime.ToString("dddd");
              }).tolist();

Error

LINQ to Entities does not recognize the method 'System.TimeSpan Subtract(System.DateTime)' method, and this method cannot be translated into a store expression

I don't want to do like below:

 var query = (from t in context.Attendance.toList().
                  select new 
                  {
                     timeDiff=t.EndDateTime.Value.Subtract(t.StartDateTime.Value).TotalHours,
                     Day=System.StartDateTime.ToString("dddd");
                  }).tolist();

I have Datetime in format like this stored in my tables and so I want expected output like below shown in difference field:

startDatetime              Enddatetime                Difference
----------------------------------------------------------------
2016-06-29 15:52:32.360    2016-06-29 15:52:36.970    00:00:04
2016-06-29 15:53:32.360    2016-06-29 15:55:36.970    00:2:00
2016-06-29 15:53:32.360    2016-06-29 16:55:36.970    01:02:00

You cannot use the difference calculation inside linq. Create a variable outside the query and then use it. — Florian Schaal, Jul 04 '16 at 14:03
@FlorianSchaal:My Both StartDateTime and EndDateTime are table fields — I Love Stackoverflow, Jul 04 '16 at 14:04
it's the Subtract that can't be used inside linq. Perform the subtraction outside linq. — Florian Schaal, Jul 04 '16 at 14:06

Jcl · Answer 1 · 2016-07-04T14:33:37.883

5

You can't use .NET framework specific methods in EF queries unless there's a canonical function defined for them. However there are, for your specific query:

Try:

 var query = (from t in context.Attendance
                  select new 
                  {                        
                     TimeDiff=SqlFunctions.DateDiff("hour",t.StartDateTime, t.EndDateTime),
                     Day=SqlFunctions.DateName("weekday", t.StartDateTime);
                  }).ToList();

Update

To reflect the changes in the wanted output in your question:

 var query = (from t in context.Attendance
                  select new 
                  {                        
                     StartDate = t.StartDateTime,
                     EndDate = t.EndDateTime,       
                     TimeDiff=SqlFunctions.DateDiff("second",t.StartDateTime, t.EndDateTime),
                  }).ToList();

And then your can use something like:

foreach(var q in query)
{
  Console.Write($"{q.StartDate:d} {q.EndDate:d} {TimeSpan.FromSeconds(q.TimeDiff):c}");
}

edited Jul 04 '16 at 14:33

answered Jul 04 '16 at 14:05

Jcl

27,696
5
61
92

1

The [`EntityFunctions`](https://msdn.microsoft.com/en-us/library/system.data.entity.core.objects.entityfunctions(v=vs.113).aspx) class was obsoleted quite some time ago, you shouldn't be recommending it. – DavidG Jul 04 '16 at 14:09
@DavidG you are indeed right, I've modified it to `SqlFunctions` for both – Jcl Jul 04 '16 at 14:10
but when i have difference in seconds then i am getting 0.For Eg in the case of this datetime(2016-06-29 15:52:32.360,2016-06-29 15:52:36.970).So when i have difference in seconds i would like to know that second.likewise if difference is of minutes then i would like to know that minutes in the format of Hour:Minutes:seconds – I Love Stackoverflow Jul 04 '16 at 14:24
@Learning your question implied you wanted the hours (because of the `.TotalHours` you wrote). Change the `"hour"``DateDiff("hour")` to the mininum you want (you can have `second`, `millisecond`, `microsecond` and `nanosecond`). You can divide that by 1000000, 1000, 60, etc, later to get the unit you want (or just convert to a `TimeSpan` afterwards if you need to output it in any format) – Jcl Jul 04 '16 at 14:27
Sorry i have updated my question with input and expected output – I Love Stackoverflow Jul 04 '16 at 14:29
@DavidG :Please see that my question is now different as i have updated it with input and expected output – I Love Stackoverflow Jul 04 '16 at 14:30
@Learning If you have changed your question so fundamentally, then you should write a new one. Editing it like that is not acceptable as it wastes everyone's time. – DavidG Jul 04 '16 at 14:33
1

@Learning I've updated my answer, but you need to make up your mind before asking a question, and include all the relevant data – Jcl Jul 04 '16 at 14:34

Calculating time difference on datetime object in Entity Framework giving error

1 Answers1

Update