CODEWITHSUNDEEP
c#linq
J.Gold
J.Gold

Reputation: 59

Linq query with SQL

I have 2 tables 'Car' and 'Booking'. The car table contains the properties CarID(PK), Make, Model and Size. The 'Booking' table contains ID(PK), CarID(FK), StartDate and EndDate.

The 2 tables contain some data that I have put in myself:

Booking - enter image description here Car - enter image description here

This is my query so far:

var searchQuery = from c in db.Cars
     from b in db.Bookings
     where c.Size == comboBox_CarType.Text
     && RentEndDate.SelectedDate < b.EndDate && RentStartDate.SelectedDate > 
     b.StartDate
     select c.CarID + " - " + c.Make.Trim() + " - " + c.Model.Trim();

The user inputs 3 pieces of information: Size, Start Date and End Date for the car they wish to rent. This will then bring up the cars available at that time from the database of cars.

My question: I have completed the size comparison however when I try to load in the available cars the above query does not bring in the vehicles, even though the logic makes sense to me

Upvotes: 0

Views: 221

Answers (4)

Louis
Louis

Reputation: 21

You can also do it this way.

 var eligibleBookings = cars.Where(r => bookings
            .Where(b => (b.StartDate <= start) && (b.EndDate <= end)&& cars
            .Where(c => c.Size == comboBox_CarType.Text)
            .Select(c => c.CarId).Distinct().ToList()
            .Contains(b.CarId))
            .Select(c => c.CarId).Contains(r.CarId));

Upvotes: 0

aperezfals
aperezfals

Reputation: 1389

You miss the inner join part, your query should be 

var searchQuery = from c in db.Cars 
     join b in db.Bookings on c.CarId= b.CarId
     where c.Size == comboBox_CarType.Text
     && RentEndDate.SelectedDate < b.EndDate && RentStartDate.SelectedDate > 
     b.StartDate
     select c.CarID + " - " + c.Make.Trim() + " - " + c.Model.Trim();

Upvotes: 1

interesting-name-here
interesting-name-here

Reputation: 1890

@Alejandro is on to it. You can use the same syntax you used but you have to join the two tables on some sort of id.

I also think you would want the start and end dates to be = as well. If the car is ready, you want to rent it right?

 var searchQuery = 
      from c in db.Cars
      from b in db.Bookings
      where c.Size == comboBox_CarType.Text
           && RentEndDate.SelectedDate <= b.EndDate 
           && RentStartDate.SelectedDate >= b.StartDate
           && c.CarId = b.CarId
      select c.CarID + " - " + c.Make.Trim() + " - " + c.Model.Trim();

Upvotes: 0

AD.Net
AD.Net

Reputation: 13399

var searchQuery = 
     from b in db.Bookings
     where b.Car.Size == comboBox_CarType.Text
     && RentEndDate.SelectedDate < b.EndDate && RentStartDate.SelectedDate > 
     b.StartDate
     select b.Car.CarID + " - " + b.Car.Make.Trim() + " - " + b.Car.Model.Trim();

Your booking should have a car property, if it doesn't work, then you have to make a join between booking and car using the carId

var searchQuery = from c in db.Cars
     join b in db.Bookings on c.CarID equals b.CarID
     where c.Size == comboBox_CarType.Text
     && RentEndDate.SelectedDate < b.EndDate && RentStartDate.SelectedDate > 
     b.StartDate
     select c.CarID + " - " + c.Make.Trim() + " - " + c.Model.Trim();

Upvotes: 1

Related Questions