Hej, próbuję pobrać listę wszystkich dostępnych pokojów w określonym terminie w danym hotelu. Niestety mam problem, ze złożeniem prawidłowego zapytania.
Początkowo próbowałem pobrać wszystkie pokoje dostępne w określonym terminie bez podania informacji z którego hotelu. Chociaż w końcowym efekcie oczekuję, że będę stanie uwzględnić w jakim hotelu należy wyszukać pokoje. (musiałbym złączyć aż 4 tabele)
Przykład zapytania, które próbowałem ułożyć:
// Klasa RoomService
public IEnumerable<Room> GetAvailableRooms(SearchRoomVM vm)
{
var availableRooms = from room in _db.Room
join reservation in _db.Reservation on room.Id equals reservation.Id
join rType in _db.RoomType on room.Id equals rType.Id
where reservation.ChkIn >= reservation.ChkOut // W tym miejscu mam problem, wiem że to odwołanie jest bezużyteczne, ale nie wiem jak to poprawić.
&& rType.NumberOfBeds == vm.NumberOfBeds
&& rType.NumberOfPeople == vm.NumberOfPeople
select room;
return availableRooms;
}
Encje:
public class Reservation
{
public int Id { get; set; }
[Required(ErrorMessage = "HotelId is required")]
[ForeignKey("Hotel")]
public int HotelId { get; set; }
public virtual Hotel Hotel { get; set; }
[Required(ErrorMessage = "User is required")]
[ForeignKey("User")]
public int UserId { get; set; }
public virtual User User { get; set; }
[Required(ErrorMessage = "Room is required")]
[ForeignKey("Room")]
public int RoomId { get; set; }
public virtual Room Room { get; set; }
[Required(ErrorMessage = "ChkIn is required")]
public DateTime? ChkIn { get; set; }
[Required(ErrorMessage = "ChkOut is required")]
public DateTime? ChkOut { get; set; }
[Required(ErrorMessage = "Status is required")]
[ForeignKey("Status")]
public int? StatusId { get; set; }
public virtual Status Status { get; set; }
}
}
public class Room
{
[Key]
public int Id { get; set; }
[Required(ErrorMessage = "Room number is required")]
public int RoomNumber { get; set; }
[Required(ErrorMessage = "Floor number is required")]
public int FloorNumber { get; set; }
[ForeignKey("RoomType")]
public int? RoomTypeID { get; set; }
public virtual RoomType RoomType { get; set; }
}
}
public class Hotel
{
[Key]
public int Id { get; set; }
[Required(ErrorMessage = "Name is required")]
[StringLength(80)]
public string Name { get; set; }
}
public class RoomType
{
[Key]
public int Id { get; set; }
[ForeignKey("Hotel")]
public int HotelId { get; set; }
[StringLength(40)]
[Required(ErrorMessage = "Room name is required")]
public string Name { get; set; }
[Required(ErrorMessage = "Area is required")]
[StringLength(60)]
public string Area { get; set; }
//[StringLength(40)]
[Required(ErrorMessage = "PriceStandardNumber is required")]
public decimal PriceStandardNumber { get; set; }
//[StringLength(40)]
[Required(ErrorMessage = "PriceSeasonNumber is required")]
public decimal PriceSeasonNumber { get; set; }
[Required(ErrorMessage = "NumberOfPeople is required")]
public int NumberOfPeople { get; set; }
[Required(ErrorMessage = "NumberOfBeds is required")]
public int NumberOfBeds { get; set; }
//[Required(ErrorMessage = "Bathroom is required")]
//public bool Bathroom { get; set; }
[Required(ErrorMessage = "Status is required")]
[StringLength(200)]
public string Description { get; set; }
public virtual Hotel Hotel { get; set; }
}
}
public class SearchRoomVM
{
[Display(Name = "Hotel")]
[Required(ErrorMessage = "Hotel is required")]
public Hotel Hotel { get; set; }
[Display(Name = "Room")]
[Required(ErrorMessage = "Room is required")]
public Room Room { get; set; }
[Display(Name = "ChkIn")]
[Required(ErrorMessage = "ChkIn is required")]
public DateTime? ChkIn { get; set; }
[Display(Name = "ChkOut")]
[Required(ErrorMessage = "ChkOut is required")]
public DateTime? ChkOut { get; set; }
[Display(Name = "NumberOfPeople")]
[Required(ErrorMessage = "ChkOut is required")]
public int NumberOfPeople { get; set; }
[Display(Name = "NumberOfBeds")]
[Required(ErrorMessage = "ChkOut is required")]
public int NumberOfBeds { get; set; }
}