Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need help with Linq

Posted on 2011-02-19
2
Medium Priority
?
393 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
2 Comments
 
LVL 27

Accepted Solution

by:
MikeToole earned 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month10 days, 1 hour left to enroll

571 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