CODEWITHSUNDEEP
c#linqentity-framework
user1968030
user1968030

Reputation:

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.

Upvotes: 1

Views: 265

Answers (4)

Omid Shariati
Omid Shariati

Reputation: 1916

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

Upvotes: 0

Gert Arnold
Gert Arnold

Reputation: 109337

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.

Upvotes: 2

D Stanley
D Stanley

Reputation: 152644

You could use TimeOfDay:

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

Upvotes: 0

Francisco Afonso
Francisco Afonso

Reputation: 247

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?

Upvotes: 1

Related Questions