Retrieving all items from one table that is not in other table with Linq

Question

I have one boat class and a rent boat class.

Boat class:

public class Boat
{
    [Key]
    public int BoatID { get; set; }

    public string BoatName { get; set; }
}

RentBoat class:

public class RentBoat
{
    [Key]
    [Required]
    public int RentBoatID { get; set; }
    [ForeignKey("Boat")]
    public int BoatID { get; set; }

    public bool IsOnRent { get; set; }

    public virtual  Boat Boat { get; set; }
}

I want to get all Boats from the database that is not in the RentBoats table.

My not working code so far:

using (var db = new BoatContext())
{
    return db.Boats.Where(boat => !db.RentBoats.Any(x => x.IsOnRent == false)).ToList();

     //(from boats in db.Boats
     //           where !db.RentBoats.Any(x => x.IsOnRent == false)
     //           select boats).ToList();
}

Haven't got any of the queries to work properly. Both of them return all the boats even if I have one item in the RentBoat table with the IsOnRent column set to 1 which is supposed to represent true in the database.

Answer 1

You should change your LINQ to the following:

db.Boats.Where(boat => !db.RentBoats.Any(x => x.BoatID == boat.BoatID &&  
x.IsOnRent == true)).ToList();

Your current query is trying to get Boats in case if all boats are on rent.