Solved

Need help with Linq

Posted on 2011-02-19
2
379 Views
Last Modified: 2013-11-11
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.
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
                          };

Open in new window

0
Comment
Question by:townsma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 27

Accepted Solution

by:
MikeToole earned 500 total points
ID: 34937218
Preselect the rooms that have the matching amenities, then use that in the query for matching dates, etc.
Attached is a class that shows how to select matching rooms as an array of RoomID.
public class LinqRoomAmenity
    {
        private List<RoomAmenity> HotelRoomAmenties;
        private List<HotelRoom> HotelRooms;
        public LinqRoomAmenity()
        {
            LoadData();
            List<string> requested = new List<string>(); 
            var RoomsWithAmenities = HotelRoomAmenties.Where(a => requested.Contains(a.Amenity)).Select(a => a.RoomID).GroupBy(id => id).Where(id => id.Count() == requested.Count());  
            // Match on 1 amenities
            requested = new List<string> { "Bed" };
            var result = RoomsWithAmenities.ToArray();
            // Match on 2 amenities
            requested = new List<string> { "Bidet", "Bed" };
             result = RoomsWithAmenities.ToArray();
            // Match on 3 amenities
            requested = new List<string> { "Bidet", "Bath", "Bed" };
             result = RoomsWithAmenities.ToArray();

        }

        internal void LoadData()
        {
            HotelRooms = new List<HotelRoom>();
            HotelRooms.Add (new HotelRoom {ID = 1}); 
            HotelRooms.Add (new HotelRoom {ID = 2}); 
            HotelRooms.Add (new HotelRoom {ID = 3}); 
            HotelRooms.Add (new HotelRoom {ID = 4}); 
            HotelRoomAmenties = new List<RoomAmenity>();
            HotelRoomAmenties.Add(new RoomAmenity { ID = 1, RoomID = 1, Amenity = "Bath"});
            HotelRoomAmenties.Add(new RoomAmenity { ID = 2, RoomID = 1, Amenity = "Bed" });
            HotelRoomAmenties.Add(new RoomAmenity { ID = 3, RoomID = 1, Amenity = "Bidet" });
            HotelRoomAmenties.Add(new RoomAmenity { ID = 4, RoomID = 2, Amenity = "Bed" });
            HotelRoomAmenties.Add(new RoomAmenity { ID = 5, RoomID = 3, Amenity = "Bed" });
            HotelRoomAmenties.Add(new RoomAmenity { ID = 6, RoomID = 3, Amenity = "Bidet" });
            HotelRoomAmenties.Add(new RoomAmenity { ID = 7, RoomID = 4, Amenity = "Bath" });
            HotelRoomAmenties.Add(new RoomAmenity { ID = 8, RoomID = 4, Amenity = "Bed" });
            HotelRoomAmenties.Add(new RoomAmenity { ID = 9, RoomID = 4, Amenity = "Bidet" });
        }
    }
    class HotelRoom
    {
        public int ID;
        public string RoomName;
        public int RoomTypeID;
    }
    class RoomAmenity
    {
        public int ID;
        public int RoomID;
        public string Amenity;
    }

Open in new window

0
 
LVL 6

Author Closing Comment

by:townsma
ID: 34941728
Worked a treat, thanks, been stuck on that one for a while.
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question