none
LINQ to Entities Compare Dates Time portion only (EF 4.1) RRS feed

  • Question

  •  c = from CDRs in c
        where EntityFunctions.DiffHours(CDRs.TimeStationOffHook, filter.StationOffHook) >= 0
        orderby CDRs.TimeStationOffHook descending
        select CDRs;
    
    I've got 2 time values that I want to compare, both are DateTime objects. I want to ignore the date, and compare only the Hour/Minute value (it's ok if seconds, milliseconds, etc get compared as well). The DiffHours function seems to be returning all records. IE: CDRs.TimeStationOffHook is at 01:00.00 and filter.StationOffHook is 14:00.00 I still get the record back. If I swap the position of the two values, no records are returned. It seems like it's comparing the Date in addition to the Hours. I tried comparing them using: where CDRs.TimeStationOffHook.Value.TimeOfDay >= filter.StationOffHook.Value.TimeOfDay but that threw an exception saying that LINQ to Entities wasn't able to translate it to a proper SQL statement. I don't want to have to write an ugly mess of comparing the .Hour and .Minute values of each in 10 different ways to make sure I get it right. I know there's got to be a better way that I'm overlooking.
    Friday, July 22, 2011 3:04 AM

Answers

  • DateTime.Compare also compares the Dates. So this won't work. I need to compare only the times.

     

    DateTime.Compare will return 1 for 9/1/2010 01:00.00 and 7/22/2011 12:00.00 as the comparisons. This is invalid and breaks the business logic.

     

    The best I've figured our so far is this:

     

    c = from CDRs in c
          where (CDRs.TimeStationOffHook.Value.Hour == filter.StationOffHook.Value.Hour && CDRs.TimeStationOffHook.Value.Minute >= filter.StationOffHook.Value.Minute) ||
            (CDRs.TimeStationOffHook.Value.Hour > filter.StationOffHook.Value.Hour)
          orderby CDRs.TimeStationOffHook descending
          select CDRs;
    

    Friday, July 22, 2011 1:30 PM

All replies

  • Hi Zachary,
    Here, I find a quick work-around for your problem, hope this help you out.
     c = from CDRs in c
      where DateTime.Compare(CDRs.TimeStationOffHook, filter.StationOffHook) >= 0
      orderby CDRs.TimeStationOffHook descending
      select CDRs;
    

    I have used DateTime.Compare method instead of EntityFunctions.DiffHours
    Thanks & Regards,
    Umesh
    -------------------------------------------------------------------------------------
    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Friday, July 22, 2011 6:24 AM
  • DateTime.Compare also compares the Dates. So this won't work. I need to compare only the times.

     

    DateTime.Compare will return 1 for 9/1/2010 01:00.00 and 7/22/2011 12:00.00 as the comparisons. This is invalid and breaks the business logic.

     

    The best I've figured our so far is this:

     

    c = from CDRs in c
          where (CDRs.TimeStationOffHook.Value.Hour == filter.StationOffHook.Value.Hour && CDRs.TimeStationOffHook.Value.Minute >= filter.StationOffHook.Value.Minute) ||
            (CDRs.TimeStationOffHook.Value.Hour > filter.StationOffHook.Value.Hour)
          orderby CDRs.TimeStationOffHook descending
          select CDRs;
    

    Friday, July 22, 2011 1:30 PM
  • Great work, thanks for sharing..
    Monday, July 25, 2011 5:48 AM