Select where not exist in interval ? Different between four dates

Question

In the first the business is car rental system .

I want to get all car where has no orders in interval selected by user

public List<Car> SearchCar(DateTime pickdate, DateTime dropdate)
{
    var db = new CarRentalDBEntities();
    var temp = new List<Car>();

    temp = db.Cars.Where(item => 

       !item.Orders.Any
        (e => e.PickUpDateTime >= pickdate && dropdate  <= e.DropDataTime)

        ).ToList();

    return temp;
}

this is the last code I write

the error is : cars still comes if order intersect with interval user choosed

You need to add more conditions; if your date range is january, youre only checkign for cars rented and returned in jan,. What about rented in jan, returned in feb.. or taken in dec, returned injan — Caius Jard, Dec 18 '17 at 09:30

Caius Jard · Accepted Answer · 2017-12-18T09:54:41.727

1

As per my comment, you probably want to be checking if either the pickup date is in the range or the return date is in the range, or if the rental period is longer than the entire range:

e => (pickdate <= e.PickUpDateTime && e.PickUpDateTime < dropdate) || //picked up in period
     (pickdate <= e.DropDataTime && e.DropDataTime < dropdate) || //dropped off in period
     (e.PickUpDateTime < pickdate && e.DropDataTime > dropdate) //rental spans period

Notes: typo in your DropDataTime

edited Dec 18 '17 at 09:54

answered Dec 18 '17 at 09:47

Caius Jard

72,509
5
49
80

Thanks peter-- think it was a highlight + ctrl-drag to copy, and I forgot to do the ctrl part so it ended up a move.. :) – Caius Jard Dec 18 '17 at 09:55

TheGeneral · Answer 2 · 2017-12-18T09:55:10.427

If i understand what you are trying to do,

I made the following modifications

I used All instead of !Any i find it easier to read
I put the Db Context In a using statement, good practice
I returned the output directly (no need for temp variable)

The premise as i understand it is to return all cars not and order

public List<Car> SearchCar(DateTime pickdate, DateTime dropdate)
{
   using (var db = new CarRentalDBEntities())
   {
       return db.Cars.Where(item => item.Orders.All(e =>
               // where order pickup date is less Pick or greater than drop
           (e.PickUpDateTime < pickdate || e.PickUpDateTime > dropdate) &&
               // where order Drop date is less Pick or greater than drop
           (e.DropDataTime < pickdate || e.DropDataTime > dropdate)))
               .ToList();
   }
}

score 0 · Answer 3 · answered Dec 18 '17 at 09:48

Maybe you can use:

var temp = db.Cars.Where(item => 
  item.Orders.All(e =>
  e.DropDataTime <= pickdate
  ||
  e.PickUpDateTime >= dropdate
  )
  ).ToList();

For all existing orders e, either they finish their order in time, or else they only need the car after.

This assumes that all existing orders e on the car are "sane" in the sense that their pick-up is earlier than their drop time.

Select where not exist in interval ? Different between four dates

3 Answers3