Solved

Need help with Linq

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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