Cannot figure out this complex query

Question

I have three tables and I am having trouble getting to the bottom of this one.

So I am given the ID of a vehicle, and I need to write a query to output a list of its seat rows.

Vehicle
{
id,
vehicleTypeId,
color
}
VehicleType
{
id,
type,
}
VehicleSeats
{
id,
description
}

This is as far as my query has gotten and I am just as a complete loss on getting this one out right. I need it to output a list of seats, not a list of types, I just do not know how to take it deeper.

var vehicleSeatsList = (from c in db.Vehicle
                                where c.VehicleTypeID == id
                                select c).ToList();

Here was my final solution. I have yet to plug it in to know if it is right. I was being a dummy. Dont know why I wasn't thinking of just doing a join...

var VehicleTypeSeats = (from c in db.VehicleTypeSeats
                                join a in db.Vehicles on c.AircraftTypeID equals a.VehicleTypeID
                                where c.VehicleTypeID == id
                                select c).ToList();

that is NOT a complex query. Is it the Vehicle ID you have (c.ID?) — ozz, Nov 16 '16 at 20:38
Yes, the VehicleID is "id". Also, I know that it isn't that complex, I just have never worked with Linq before. — Travis Tubbs, Nov 16 '16 at 20:48
When you have EF related question, post your entity **classes**, not just table and column names. It would be much easier for you and for the people trying to help you. — Ivan Stoev, Nov 16 '16 at 20:49
Look at https://stackoverflow.com/questions/2767709/c-sharp-joins-where-with-linq-and-lambda - you really just need to do a join. — AllMadHare, Nov 17 '16 at 01:28

score 0 · Answer 1 · answered Nov 16 '16 at 20:45

0

Assuming this is in SQL, there is no relationship between your VehicleSeats and Vehicle tables. You would need to add VehicleSeats.vehicleID or something like that. If you did, the below would work:

SELECT VehicleSeats.description FROM VehicleSeats, Vehicle WHERE VehicleSeats.vehicleID = Vehicle.id AND Vehicle.id = {your given vehicle ID};

answered Nov 16 '16 at 20:45

frostfat

155
3
13

Im doing it in linq and that is the part that I am confused about. – Travis Tubbs Nov 16 '16 at 20:49
Sorry dude no experience with that – frostfat Nov 16 '16 at 20:58

score 0 · Answer 2 · answered Nov 17 '16 at 13:09

Simply inner join Vehicle and Vehicle seats on their common column then add the where statement.

var VehicleTypeSeats = (from c in db.VehicleTypeSeats
   join a in db.Vehicles on c.AircraftTypeID equals a.VehicleTypeID
   where c.VehicleTypeID == id
   select c).ToList();

Cannot figure out this complex query

2 Answers2