LINQ Correlated Subquery

Question

Two tables - Vehicles and Agreements. I want the subquery to check that my vehicle does not have an existing agreement. I have spent all day trying to implement the various solutions found on-line but I can't get this query to work. The problem with the version below is that the 4th line t1 doesn't exist in the scope. I understand why that is but I don't know what I need to do differently. Can anyone help a budding new .net developer please? :o)

var cars = from t1 in db.VEHICLEs
                   && !(from t2 in db.AGREEMENTs
                           where t2.STATUS_OPEN == true
                           where t1.CAR_ID == t2.CAR_ID
                           where enDate > t2.HIRE_START_DATE
                           where enDate < t2.HIRE_END_DATE
                           select t2)                               
                           select t1;

Answer 1

Try using Any. Something along the lines of -

var cars =  from v in db.VEHICLEs
   where !AGREEMENTs.Any(a => (a.CAR_ID == v.CAR_ID 
   && a.STATUS_OPEN
   && enDate > v.HIRE_START_DATE
   && enDate < v.HIRE_END_DATE)