townsma
asked on
Need help with Linq
I am reasonably comfortable with LINQ, in particular the INQ for Lightspeed which I use, but I have come up against a problme I am just unable to get my head around, so I am hoping someone can help me.
Firstly the tables
HotelBooking, a table that contains all bookings made for a hotel. The important fields are:
Id int keyfield
RoomTypeId int FK to RoomType table
RoomId int FK to Room table
ArrivalDate datetime
DepartureDate datetime
Status int
HotelRoom, table contains all rooms in the hotel
Id int Keyfield
RoomName string
RoomTypeId int FK to RoomTypeTable
HotelRoomAmenties, table containing the facilitoes / amenities available in each room, i.e. Smoking, Sea View, Internet etc
Id int KeyField
RoomId int FK to HotelRoom
Amentity string Amenity name
Paramaters passed to LINQ
RoomType int Limit the search to only rooms/bookings of this type.
Arrival / Departure Date datetimes I need to know about all existing bookings within this time frame.
Amenities List<String> contains one value for each required room amentity
Below is the LINQ i use currently, which seems to work, however, the Amenties paramaters hve not yet been incorporated..
What I need it a list of HotelRooms that are of the correct type, along with the bookings currently in the system for the selected rooms and that overlap the supplied dates. But I can not see how to do a r.Amenities in <List> amenities type criteria.
I would be eternally grateful for anyone who can assist with this.
Many thanks.
Firstly the tables
HotelBooking, a table that contains all bookings made for a hotel. The important fields are:
Id int keyfield
RoomTypeId int FK to RoomType table
RoomId int FK to Room table
ArrivalDate datetime
DepartureDate datetime
Status int
HotelRoom, table contains all rooms in the hotel
Id int Keyfield
RoomName string
RoomTypeId int FK to RoomTypeTable
HotelRoomAmenties, table containing the facilitoes / amenities available in each room, i.e. Smoking, Sea View, Internet etc
Id int KeyField
RoomId int FK to HotelRoom
Amentity string Amenity name
Paramaters passed to LINQ
RoomType int Limit the search to only rooms/bookings of this type.
Arrival / Departure Date datetimes I need to know about all existing bookings within this time frame.
Amenities List<String> contains one value for each required room amentity
Below is the LINQ i use currently, which seems to work, however, the Amenties paramaters hve not yet been incorporated..
What I need it a list of HotelRooms that are of the correct type, along with the bookings currently in the system for the selected rooms and that overlap the supplied dates. But I can not see how to do a r.Amenities in <List> amenities type criteria.
I would be eternally grateful for anyone who can assist with this.
Many thanks.
var bkngs = from b in uow.HotelBookings.Where(x => x.RoomTypeId == roomTypeId
&& (
(x.CheckInDate >= arrivalDate &&
x.CheckInDate <= departDate)
||
(x.CheckOutDate >= arrivalDate &&
x.CheckOutDate <= departDate)
)
&& x.Status != (int)Enums.RoomBookingStatus.Cancelled
)
join r in uow.HotelRooms on b.RoomId equals r.Id into z
from r in z.DefaultIfEmpty()
join t in uow.HotelRoomTypes on b.RoomTypeId equals t.Id into y
from t in y.DefaultIfEmpty()
select new HotelRoomCalendarSearch
{
DateIn = b.CheckInDate,
DateOut = b.CheckOutDate,
FolioId = b.GuestFolioId,
GuestName = b.GuestName,
Id = b.Id,
RoomId = b.RoomId,
RoomName = r.RoomName,
Status = b.Status
};
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER