Count() not support in LINQ to Lightspeed

 I have a LINQ query that worked fine, until I tried to add a count function into it.  Now I recieve a Count() not support in LINQ Lightspeed error message.  Can anyone please suggest a workaroud that might work.

My LINQ in below, the line with the problem is highlighted.

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.Where(hr => hr.HotelRoomAmenities.Count(hra => hra.Amenity == Amenity1.Amenity) > 0) 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.Status == (int) Enums.RoomBookingStatus.Occupied ||
                            b.Status == (int) Enums.RoomBookingStatus.Reserved
                            ? b.GuestName : "",
                Id = b.Id,
                RoomId = b.RoomId,
                RoomName = b.Status == (int) Enums.RoomBookingStatus.Maintenance ? b.WorkOrderNo : r == null ? "" : r.RoomName,
                Status = b.Status
               };

var tmplist = new BindingList<HotelRoomCalendarSearch>(bkngs.ToList());

The problem line highlighted with ***** is join r in uow.HotelRooms.Where(hr => hr.HotelRoomAmenities.Count(hra => hra.Amenity == Amenity1.Amenity) > 0) on b.RoomId equals r.Id into z

What I am trying to do is limit the HotelRooms collection to rooms that contain a record in the joined collection, HotelRoomAmenities, that matches a passed string value, i.e. Amenity1.Amenity.  But LINQ for lightspeed does not seem to support this functionality.  Can anyone suggest another way to do this?

I have been looking at this for a while, and I am loosing my mind over it.

Thanks in advance to all helpers.

Hi Mark,

Count() (and other SQOs) are not supported as criteria in LightSpeed, so having them in the Where clause is what is causing the error.

Rather than filtering based on the Where approach, try joining the HotelRoomAmenities and then filter directly against that, e.g.

join hrm in uow.HotelRoomAmeneties on r.Id equals hrm.HotelId

...

where hrm.Amenity == Amenity1.Amenity

Jeremy