Ytukyg

I am making a hotel reservation system in windows form by using SQL with LINQ. I have tables such as reservation tabel and room table. In reservation table I have checkin and checkout dates. I have no problem to show those rooms from reservation table in a specific date, but I don't know how to show rooms that are available on specific dates. I don't how to compare or filter out RoomsId from reservation table and room table.

Here is how I am getting rooms from reservation table on a specific date (checkin and checkout):

DateTime StartDateWantToBook = Convert.ToDateTime(dateTimePicker1.Value.ToString());

DateTime EndDateWantToBook = Convert.ToDateTime(dateTimePicker2.Value.ToString());



var ReservedRooms = (from u in db.Room join b in db.Reservation on u.RoomId equals b.RoomId join f in db.Floor on u.FloorId equals f.FloorId join ty in db.RoomType on u.RoomTypeId equals ty.RoomTypeId where StartDateWantToBook <= b.EndDate && b.StartDate <= EndDateWantToBook

 select new {

  RomId = b.RoomId,

   Floor = f.FloorName,

   RommsNr = u.RumNummer,

   Room_Type = ty.AmountRomms

   // But Here by somehow I think I have to run

   // another Linq query to filter RoomsId and show only those who do not exists in Reservation table.                             

 }

).ToList();

dataGridView1.DataSource = ReservedRooms;

So the question is How to show all rooms in Rooms table that do not exist in Reservartionroom in a specific dates. Thank you again!

This must work for you.

var AvailebleRooms = (from u in db.Room

                      where (!u.Reservation.Any(b => b.EndDate >= StartDateWantToBook && b.StartDate <= EndDateWantToBook))

                      select u).ToList();

i think you need left outer join AllRooms and ReservedRooms to get rooms only using where clause with specific dates

You need to select AllRooms, ReservedRooms in a specific date and filter AllRoom by list reservartion. That's my Idea, maybe contains is wrong.

DateTime StartDateWantToBook = Convert.ToDateTime(dateTimePicker1.Value.ToString());

DateTime EndDateWantToBook = Convert.ToDateTime(dateTimePicker2.Value.ToString());



var AllRooms = (from u in db.Room join b in db.Reservation on u.RoomId equals b.RoomId join f in db.Floor on u.FloorId equals f.FloorId join ty in db.RoomType on u.RoomTypeId equals ty.RoomTypeId select new {

 RomId = b.RoomId,

  Floor = f.FloorName,

  RommsNr = u.RumNummer,

  Room_Type = ty.AmountRomms

  // But Here by somehow I think I have to run

  // another Linq query to filter RoomsId and show only those who do not exists in Reservation table.                              

}).ToList();



var ReservedRooms = (from u in db.Room join b in db.Reservation on u.RoomId equals b.RoomId join f in db.Floor on u.FloorId equals f.FloorId join ty in db.RoomType on u.RoomTypeId equals ty.RoomTypeId where StartDateWantToBook <= b.EndDate && b.StartDate <= EndDateWantToBook select new {

 RomId = b.RoomId,

  Floor = f.FloorName,

  RommsNr = u.RumNummer,

  Room_Type = ty.AmountRomms

  // But Here by somehow I think I have to run

  // another Linq query to filter RoomsId and show only those who do not exists in Reservation table.



}).ToList();



var result = AllRoom.Except(ReservedRooms);

dataGridView1.DataSource = ReservedRooms;