question about linq query to run in c#

User 11570154

hi there I have a simple question I have 2 very simple table for booking Cars (cars and booking ) cars has this field ID (PK) , Make, Model , Size and Booking this field ID (Pk) ,carID (FK) ,startRental, endRental I know in linq you don't have between like in SQL , I need to retrieve all the cars available that are not booked on the startDate and EndDate any Idea ? cheers

Sascha Lefevre

BETWEEN in SQL is just "syntax sugar" - you can express it in LINQ simply with something like this:

someValue >= x && someValue <= y

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

User 11570154

thanks for the reply I have this code but this one will retrieve the object for those dates and I need the object that are not booked for those days

Sascha Lefevre

Did you attempt to solve it? Suggestion: It becomes easier if you determine those cars that are booked in that time frame and then select the other cars.

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

User 11570154

I was trying to add all the cars booked to a collection and all the cars available to a second collection and the comparing the two collection and not listing out the one that match but I can't really get it work thanks for your time cheers

Sascha Lefevre

You could post your code and I'll try to tell you why it doesn't work. Maybe this pseudo-code helps?:

foreach car in allCars
{
foreach booking in car.Bookings
{
if (booking overlaps with requested time frame)
{
unavailableCars.Add(car)
break
}
}
}
availableCars = allCars.Except(unavailableCars)

alternatively:

availableCars = allCars.Except(allCars.Where(car => car.Bookings.Any(bkg => bkg overlaps with requested timeframe)))

A booking does not overlap with a requested time frame if:

bookingEndDate < requestStartDate || bookingStartDate > requestEndDate

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

User 11570154

//query to get all the carId make and model booked on those days
var query1 = from b in db.Bookings
where b.StartDate >= Start &&
b.EndDate <= End
select new { Make = b.Car.Make, Model = b.Car.Model, CarID = b.CarID };

                    //ad all the boked car to a collection called carBooked
                    foreach (var c in query1)
                    {
                        Car car1 = new Car(c.Make, c.Model, c.CarID, Start, End);
                        carSizeTotal.Add(car1);

                    }


                    //query to get all the car available 
                    var query = from c in db.Cars
                                select new { Make = c.Make, Model = c.Model, CarID = c.ID };



                    //adding all the cars to a collection 
                    foreach (var c in query)
                    {
                        Car car = new Car(c.Make, c.Model, c.CarID, Start, End);
                        carSizeTotal.Add(car);
                    }

does one are the 2 query I have to get all the cars booked and all the cars in the cars table then I just need to nested a foreach loop to eliminate the already bookede

User 11570154

                    LbxAvalCars.ItemsSource = carSizeTotal;

                    RentCarDBEntities db = new RentCarDBEntities();


                    //query to get all the carId make and model booked on those days
                    var query1 = from b in db.Bookings
                                 where b.StartDate >= Start &&
                                 b.EndDate <= End
                                 select new { Make = b.Car.Make, Model = b.Car.Model, CarID = b.CarID };

                    //ad all the boked car to a collection called carBooked
                    foreach (var c in query1)
                    {
                        Car car1 = new Car(c.Make, c.Model, c.CarID, Start, End);
                        carBooked.Add(car1);

                    }


                    //query to get all the car available 
                    var query = from c in db.Cars
                                select new { Make = c.Make, Model = c.Model, CarID = c.ID };



                    //adding all the cars to a collection 
                    foreach (var c in query)
                    {
                        Car car = new Car(c.Make, c.Model, c.CarID, Start, End);
                        carSizeTotal.Add(car);
                    }

sorry this one is correct one the other one has some errors

Sascha Lefevre

Your first query doesn't accurately return those cars that are booked in the requested time frame. Assuming it would, you then add all booked cars to carSizeTotal and then all cars to carSizeTotal ...that doesn't help, you could as well have skipped adding the booked cars. Take a look at my last message, I think you should be able to figure it out - you're not too far away :)

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

Sascha Lefevre

Looks like the code you posted first just with the two additional first lines - so my reply should be still valid :)

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

User 11570154

thanks I'll give it a go and see if I can get it work thanks for your help cheers

Sascha Lefevre

You're welcome! Feel free to ask again if you can't get it to work.

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

User 11570154

Try all day couldn't get it work

Sascha Lefevre

Alright.. here you go:

LbxAvalCars.ItemsSource = carSizeTotal;

RentCarDBEntities db = new RentCarDBEntities();

var bookedCars = from b in db.Bookings
where b.StartDate <= End && b.EndDate >= Start // disallow same day
//where b.StartDate < End && b.EndDate > Start // allow same day
select b.Car;

var availableCars = db.Cars.Except(bookedCars);

foreach (var car in availableCars)
{
carSizeTotal.Add(new Car(car.Make, car.Model, car.CarID, Start, End));
}

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

User 11570154

thanks really appreciate your help cheers