0

when i tried to group by IQueryable i found that query was very very slowly and when i removed group by it return fast can any one help me

        public async Task<GridOutPut<ticketSearchRes>> ticketGridView<T>(GridViewEntity gridViewInputsVM, gridViewSearch searchRes , int CurrentuserId)
        {
            List<ticketSearchRes> tickets = new List<ticketSearchRes>();
            IQueryable<ticketSearchRes> source = (from ticket in _db.TblTicket
                                                  join ticketCycle in _db.TblTicketCycle on ticket.TicketId equals ticketCycle.TicketId where ticketCycle.IsVisable == 1
                                                  join ticketUser in _db.TblTicketUser on ticket.TicketId equals ticketUser.TicketId
                                                  where ticketUser.UserId == CurrentuserId || (ticketUser.UserId != CurrentuserId && ticket.CreatedBy == CurrentuserId)
                                                  group ticket by new { ticket.TicketId } into groupedTickets // grouped by here
                                                  select new ticketSearchRes
                                                  {
                                                      title = (groupedTickets.FirstOrDefault().TicketTitle != null) ? groupedTickets.FirstOrDefault().TicketTitle.ToString() : "",
                                                      ticId = groupedTickets.FirstOrDefault().ToString(),
                                                      encTicId = (groupedTickets.FirstOrDefault().TicketId.ToString() != null) ? groupedTickets.FirstOrDefault().TicketId.ToString() : "",
                                                      createdBy = (groupedTickets.FirstOrDefault().CreatedBy.ToString() != null) ? groupedTickets.FirstOrDefault().CreatedBy.ToString() : "",
                                                                                                      }
                                                ).AsQueryable();

            if (searchRes.title != "") // ticket title search for ID || Title || Description
            {
                source = source.Where(a => a.title.ToLower().Trim().Contains(searchRes.title) || a.description.ToLower().Trim().Contains(searchRes.title) || a.ticId.ToLower().Trim().Contains(searchRes.title));
            }
            if (searchRes.ticStatus != 0) { // ticket status
                source = source.Where(a => a.status == searchRes.ticStatus.ToString());
            }
            if (searchRes.ticCategory != 0) // ticket category 
            {
                source = source.Where(a => a.categoryId == searchRes.ticCategory.ToString());
            }
            return await source.SortFunctionAsync(tickets, gridViewInputsVM);
        }
Peter B
  • 22,460
  • 5
  • 32
  • 69
Fatma Gomaa
  • 1
  • 2
  • 1
    It's not possible to say the impact of group by without knowing the execution plan of the query and the data structure. I suggest you investigate the sql server execution side of the query. – Ahmet Jul 23 '19 at 12:45
  • See [How do you show underlying SQL query in EF Core 2.0?](https://stackoverflow.com/q/45893732/1220550) to inspect the generated SQL. If there is no GROUP BY in there, then the query generator was unable to do so and then it groups client-side. If the dataset is large then that may cause slowness. – Peter B Jul 23 '19 at 12:54
  • Grouping is an expensive operation on the database end. Without knowing what your database looks like and what indexes you've setup, it will be difficult to determine. Why not just group on the client side after the data has arrived (assuming its not an overwhelming amount).Reference :[Group by in LINQ](https://stackoverflow.com/a/7325306/10201850) – Xueli Chen Jul 24 '19 at 03:26

0 Answers0