0

I'm using ASP.NET MVC, C# and SQL Server.

I want to get a list of cars that are available for rent within a certain date, ie. not already rented out.

What I got now is something like taking all the cars from the cars table, except the ones that have an order at the specified date (instead of checking each car individually for an order):

var query = db.Cars.Select(r => r.ID)
    .Except(db.Orders
        .Where(o => (startDate >= o.RentalStart 
            && startDate <= o.RentalReturn))
                Select(r => r.Car.ID));

The problem is that I want to return a custom object, and I'm not sure how do that using except, so I could always get the ID, and based on the ID get the entire object with a different method/query.

But I'd like to get something like:

Select(c => new myModel
{
    ID = c.ID,
    property1 = c.property1,
    property2 = c.property2,
    property3 = c.property3,
    property4 = c.property4,
});
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
CAR1980
  • 33
  • 5

1 Answers1

0

What you can do is filter the Cars list where the ID is not contained in the set of invalid order IDs.

var invalidOrders = db.Orders
    .Where(o => startDate >= o.RentalStart && startDate <= o.RentalReturn)
    .Select(r => r.Car.ID)

var query = db.Cars.Where(car => !invalidOrders.Contains(car.ID));
Servy
  • 202,030
  • 26
  • 332
  • 449
  • thanks :) I got the first part my self, but only half of the second..that did the job my next question is, is it possible to do all that in one query? – CAR1980 Aug 25 '14 at 18:27