How do I select all objects from a list that have no related objects in another list/model

Question

Using linq,how do I select all Room objects from a list of Room objects for which no Booking objects exist in the Booking model (can check by foreign key)?

This is the list of the objects:

var rooms = (from m in db.Rooms
             where
             m.FK_HotelID == id
             select m).ToList();

This is the first model called Room:

public partial class Room
{
    public Room()
    {
        Bookings = new HashSet<Booking>();
    }
    public int RoomID { get; set; }
    public int RoomNumber { get; set; }
    public string RoomType { get; set; }
    // Foreign Key
    public int? FK_HotelID { get; set; }
    public virtual ICollection<Booking> Bookings { get; set; }
    // Navigation Property
    public virtual Hotel Hotel { get; set; }
}

And a second model called Booking:

public partial class Booking
{
    public int BookingID { get; set; }
    public string BookingName { get; set; }
    public string BookingContact { get; set; }
    public DateTime BookingTime { get; set; }
    public string BookingStatus { get; set; }
    // Foreign Key
    public int? FK_RoomID { get; set; }
    // Navigation Property
    public virtual Room Room { get; set; }
}

I am a beginner to linq and all I can understand so far is how to select from a single model, although I can easily get the result using a SQL query with a left join.

P.S: I made the database first and generated the models using the ADO.NET Entity Data Model Wizard with Code First from Database. I also don't mind making any small changes to the database or model as long as I understand why.

Answer 1

This should work:

var unbookedRooms = db.Rooms.Where(r =>  r.FK_HotelID == id && !r.Bookings.Any()).ToList();

The idea is to use LINQ's Any() method on the child collection to exclude Room objects with non-empty Booking collections.