My project is hotel reservation. And so I went to the place where the problems started) I have a Бронирование table and there is a Комната table. They are КомнатаБронь through the КомнатаБронь table, which takes Id armor and Id rooms.

So I need to display rooms that are not yet booked by this date. But I do not know how to link through an intermediate table.

Here is something tried datepicker navayat - incoming date, as well as the number of nights transmitted, it also needs to somehow insert

 from p in context.Room join c in context.Booking on p.Id equals c.Id where (c.DateOfEntry between c.DateOdOut) != datepicker
  • You need a schema of tables containing the attributes you need to query. - BuilderC
  • And why not just remove the RoomBooking table by moving the RoomId to the Booking table? In the current version, it is an unnecessary complication, which does not introduce any advantages. - Sergey Rufanov 5:56 pm
  • I updated and added screen - andrey1567
  • Yes, it would be easier, but it is impossible to return such a task back - andrey1567

1 answer 1

Something like this:

 // Планируемая дата. Вы её можете взять из datepicker. // Просто для теста запроса задал её статично. var desiredDate = new DateTime(2015, 5, 11); // Находим все комнаты, которые заняты в указанную дату var occupiedRooms = from booking in context.Booking join roomBooking in context.RoomBooking on booking.Id equals roomBooking.BookingId join room in context.Room on roomBooking.RoomId equals room.Id where booking.DateOfRegistration <= desiredDate && booking.DateOfRegistration.AddDays(booking.NumbetOfNighs) > desiredDate select room; // Получаем все комнаты кроме занятых var freeRooms = context.Room.Except(occupiedRooms);

Well, then bring this list of rooms to where you need.

Ideally, this code will generate only one database request.

  • DateOfRegistration is the date the order was created, you probably meant DateOfEntry (this is the date of arrival)? - andrey1567
  • @ andrey1567, well, it's just confusing with terminology. I thought that the check-in date is the check-in date at the hotel, i.e. exactly when you entered and registered at the reception (as actually everywhere). And the date of the entity is the date when the entity (booking) was recorded in the database (or in the reservation book). - Sergey Rufanov
  • Aha) Well, this is my mistake, did not clarify) - andrey1567
  • join roomBooking in context.RoomBooking I can’t do this ((Updated - added models - andrey1567