Solved

Need help with Linq

Posted on 2011-02-19
2
372 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

726 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