How can i optimize my entity framework query for better performance and more readability

Question

There is a heavy page in my site called HotelDetail, that several things related to hotel will be load in this page. hotel, hotel's services,destinations of places to hotel,user's comments,hotel's room with their Services, prices and capacity. All of this will be load in one time with below code, now I want to optimize this code, what's solution to optimize this for better performance and more readability ? is there a way to not load all of then in one time?

  public PlaceViewModel GetPlaceDetail(string languageKey,string catKey , string placeKey, SearchViewModel searchOptions)
        {
                DateTime startDate = searchOptions.CheckIn.ToMiladiDateTime();
                DateTime endDate = searchOptions.CheckOut.ToMiladiDateTime();
                int nights = (int)endDate.Subtract(startDate).TotalDays;
                placeViewModel.NightCount = nights;
                var query = (from r in _unitOfWork.RoomServiceRepository.Get()
                             join
                                a in _unitOfWork.InventoryRepository.Get()
                                on r equals a.RoomService
                             where r.Room.Place.Id == place.Id && !r.SoftDelete && !r.Room.SoftDelete &&
                                   a.Date >= startDate && a.Date < endDate && (a.CertainAvailability + a.FloatAvailability) > 0
                             select new { a, MaxCapacity = r.Room.Capacity + r.ExtraCapacity, r.RoomId });

                placeViewModel.HasAvailability = query.ToList().Count != 0;

               var grp = query.GroupBy(y => y.a.RoomServiceId)
                    .Select(z => new {
                        key = z.Key,
                        count = z.Count(),
                        minAvail = z.Min(ax => ax.a.CertainAvailability + ax.a.FloatAvailability),
                        minDate = z.Min(y => y.a.Date),
                        minPrice = z.Min(ax=>ax.a.Price),
                        price = z.Average(ax=>ax.a.Price) });

                var tmp = query.Where(x => grp.Any(q => 
                    q.key == x.a.RoomServiceId && 
                    q.count == nights &&
                    q.minDate == x.a.Date)).ToList();
                foreach (var item in tmp)
                {
                    var roomViewModel = placeViewModel.Rooms.FirstOrDefault(x => x.Id == item.RoomId);
                    var avail = new AvailabilityViewModel(item.a, item.MaxCapacity);
                    avail.Availability = grp.FirstOrDefault(x => x.key == item.a.RoomServiceId).minAvail;
                    var otherInfos = grp.SingleOrDefault(x => x.key == item.a.RoomServiceId);
                    avail.JabamaPrice = otherInfos.price;
                    avail.JabamaMinPriceInPeriod = otherInfos.minPrice;
                    roomViewModel.Availabilites.Add(avail);
                    if (maxDiscount == null || (maxDiscount != null &&(maxDiscount.BoardPrice - maxDiscount.JabamaPrice) < (avail.BoardPrice - avail.JabamaPrice)))
                        maxDiscount = avail;
                    if (minPrice == null || (minPrice != null && avail.JabamaPrice > 0 && minPrice.JabamaPrice > avail.JabamaPrice))
                        minPrice = avail;
                }
                var discountQuery = tmp.Where(x => x.a.RoomService.ExtraCapacity == 0 && x.a.Date == minPrice.Date);
                try
                {
                    if (discountQuery.Any())
                        maxDiscountOfMinPercentageDay = tmp != null && minPrice != null ? discountQuery.Max(x => (x.a.BoardPrice - x.a.Price) / x.a.BoardPrice * 100) : 0;
                    else
                        maxDiscountOfMinPercentageDay = 0;

                }
                catch (Exception)
                {
                    maxDiscountOfMinPercentageDay = 0;
                }
                foreach (var roomVM in placeViewModel.Rooms)
                {
                    if (roomVM.Availabilites.Count() == 0)
                        roomVM.Availabilites.Add(new AvailabilityViewModel(-1));
                }
}

This will definitely depends on how you consume the data in the UI. You should only get what you display at glance. — Hassan, Jul 16 '16 at 11:48
Actually all of them have to be show at one glance, but I need to optimize this code too, it became complicated, — samira riazati, Jul 16 '16 at 11:54
You need to be more explicit of what type of optimization you are looking for: Performance, readability, scalability etc... — Hassan, Jul 16 '16 at 12:05
One quick win is to store the result of `query.ToList()` in a variable and work with that in-memory list onward. — Gert Arnold, Jul 18 '16 at 21:06

score 0 · Answer 1 · answered Jul 18 '16 at 09:28

I'm far from familiar with Linq but I do know some of the pitfalls of SQL. One of the things I notice is that you (and many other people!) use .Count != 0 to figure out if there might or might not be a matching record. In SQL this is a very inefficient approach as the system will effectively go over the entire table to find and count the records that match the prerequisite. In such cases I'd advice to use a WHERE EXISTS() construction; I believe Linq has something along the lines of .Any() that works similarly.

This is also what you do in real life: when someone asks you if there is any cheese left in the fridge, you will not start counting all types of cheese you can find in there but rather stop after the first type of cheese you see and answer 'Yes there is'; there is no added value to going through the entire fridge, it would only cost extra time.

PS: This probably also is true for roomVM.Availabilites.Count() == 0 which might be better handled with inversed logic...

Disclaimer: it could be that the EntityFwk is smart enough to optimize this for you in the background... I don't know; I very much doubt it but like I said, I'm no specialist. I'd suggest to start by first figuring out how long each part of your current code takes and then optimize the slowest ones first. Having a baseline also gives you a better idea if your changes are having any effect, be it positive or negative.

How can i optimize my entity framework query for better performance and more readability

1 Answers1