How to extract records which are between specified hours of the day in Entity Framework?

I have a table of Orders, and it has a field called OrderDateTime.

I want to apply two filters on my query:

  1. Extracting orders which have happened from a given fromDate to the given toDate
  2. Only showing orders which have happened from a given fromTime up to the given toTime

For example, the query might be:

Show last week orders which have happened only from 10 in the morning up to 2'o clock after noon

What I've done now is this query:

return Orders
       .Where(o => o.OrderDateTime > fromDate
                && o.OrderDateTime < toDate
                && o.OrderDateTime.Hour > fromTime.Hours
                && o.OrderDateTime.Minute > fromTime.Minutes
                && o.OrderDateTime.Hour > toTime.Hours
                && o.OrderDateTime.Minute > toTime.Minutes
             );

But I don't get any record, while I do have orders in that time. What's wrong with my query?

Note: fromTime and toTime are TimeSpan objects.

Answers


first answer is to use

return Orders
  .Where(o => o.OrderDateTime > fromDate
      && o.OrderDateTime < toDate
      && o.OrderDateTime.TimeOfDay.TotalMinutes > fromTime.TimeOfDay.TotalMinutes
      && o.OrderDateTime.TimeOfDay.TotalMinutes < toDate.TimeOfDay.TotalMinutes
   );

!!!!! BUT WAIT, IT'S NOT WORK ON LINQ TO ENTITY !!!!!

because Linq to entity does not support TimeOfDay!

ok nop. my solusion is to calculate TimeOfDay in a specified units for example Minutes or Seconds or Milisecends... for your problem I think minute is good.

return Orders
  .Where(o => 
      o.OrderDateTime > fromDate && o.OrderDateTime < toDate
      && (
          o.OrderDateTime.Hours * 60 + o.OrderDateTime.Minutes
           > 
          fromTime.Hours * 60 + fromTime.Minutes
         )
      && (
         o.OrderDateTime.Hours * 60 + o.OrderDateTime.Minutes
          <
          toTime.Hours * 60 + toTime.Minutes
         )
   );

hope it helps


First issue is that you use > in stead of <. At first glance it should be:

    ...
    && o.OrderDateTime.Hour < toTime.Hours
    && o.OrderDateTime.Minute < toTime.Minutes
);

But that excludes all data with minutes < toTime.Minutes, also the ones that are before toTime.Hours. So you must do this:

&& o.OrderDateTime.Hour * 100 + o.OrderDateTime.Minute 
       > fromTime.Hours * 100 + fromTime.Minutes
&& o.OrderDateTime.Hour * 100 + o.OrderDateTime.Minute
         < toTime.Hours * 100 + toTime.Minutes

And for EF you must make variables of fromTime.Hours * 100 + fromTime.Minutes and toTime.Hours * 100 + toTime.Minutes and put those in the linq statement.

Maybe you should use >= and <= for the hour and minute comparisons.


I think you wanted this:

    return Orders
   .Where(o => o.OrderDateTime > fromDate
            && o.OrderDateTime < toDate
            && o.OrderDateTime.Hour > fromTime.Hours
            && o.OrderDateTime.Minute > fromTime.Minutes
            && o.OrderDateTime.Hour < toTime.Hours
            && o.OrderDateTime.Minute < toTime.Minutes
         );

Last two < signs were > in your example

@EDIT: Also, why dont you use a DateTime column instead so you can compare all that in a single data variable?


You could use TimeOfDay:

return Orders
.Where(o => o.OrderDateTime > fromDate
        && o.OrderDateTime < toDate
        && o.OrderDateTime.TimeOfDay > fromTime
        && o.OrderDateTime.TimeOfDay < toTime 
     );

Need Your Help

Flash Media Server, HLS and FLV

ios streaming flv rtmp flash-media-server

I use RTMP to stream from my iPhone to my server with FMS. I followed some tutorials and now I have the flv playback file in /webroot/live_recorded.

capturing dis.dis results

python python-2.7

Is there any way to get the output of dis.dis() without redirecting sys.stdout? I have tried:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.