Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need help with Linq

Posted on 2011-02-19
2
Medium Priority
?
392 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 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

610 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