title: Slow LINQ Query on Join
body: <p>I'm using EF 6 with .NET Core. I have written the following query to select some data into a custom object. At the moment the main query returns over 25000 records. Then the filters are applied. This query is running very slow. I'm just trying to find out if I'm doing any mistakes here. Why it could be so slow and is the filtering happening on the memory rather than on the database side?</p>
  
  <pre><code>public IQueryable&lt;TicketViewModel&gt; GetTickets(int companyId, string locale, TicketFilterViewModel filters, Guid customerRef, int? take, int currentUser)
          {
              IQueryable&lt;TicketViewModel&gt; tickets = (from to in _context.Ticket
                                                     join co in _context.Company on to.CompanyId equals co.CompanyId
                                                     join con in _context.Contact on to.ContactId equals con.ContactId
                                                     join site in _context.Site on to.SiteId equals site.SiteId
                                                     join country in _context.Country on site.CountryId equals country.CountryId
                                                     join cus in _context.Customer on con.CustomerId equals cus.CustomerId
                                                     join tic_type in _context.TicketType on to.TicketTypeId equals tic_type.TicketTypeId
                                                     join ts in _context.TicketStatus on to.TicketStatusId equals ts.TicketStatusId
                                                     join sb in _context.ServiceBoard on to.ServiceBoardId equals sb.ServiceBoardId into ob1
                                                     from a in ob1.DefaultIfEmpty()
                                                     join u in _context.User on to.TechnitianId equals u.Id into ob2
                                                     from b in ob2.DefaultIfEmpty()
                                                     join pr in _context.Priority on to.PriorityId equals pr.PriorityId into ob3
                                                     from c in ob3.DefaultIfEmpty()                                                  
                                                     where to.CompanyId == companyId &amp;&amp; (customerRef == Guid.Empty || cus.RefNo == customerRef)
                                                     &amp;&amp; to.MergedIntoTicketId == null
                                                     select new TicketViewModel
                                                     {
                                                         CreatedOn = Helpers.Custom.UtcToStandardTime(locale, to.AddedOnUtc).ToString("dd/MM/yyyy hh:mm tt"),
                                                         CustomerName = cus.CustomerName,
                                                         TicketNumber = to.TicketNumber,
                                                         TicketTitle = to.TicketTitle,
                                                         RefNo = to.RefNo,
                                                         CompanyName = co.CompanyName,
                                                         ContactName = string.Concat(con.FirstName, " ", con.LastName),
                                                         SiteAddress = String.Concat(site.AddressLine1, ", ", site.AddressLine2, ", ", site.Suburb, ", ", site.State, ", ", site.PostCode, ", ", country.Name),
                                                         ServiceBoardName = a.BoardName,
                                                         TechnicianName = string.Concat(b.FirstName, " ", b.LastName),
                                                         PriorityName = c.PriorityName,
                                                         TicketStatus = ts.StatusName, //d.StatusName,
                                                         ServiceBoardId = to.ServiceBoardId,
                                                         TicketStatusId = to.TicketStatusId,
                                                         CustomerId = cus.CustomerId,
                                                         PriorityId = to.PriorityId,
                                                         ContractId = site.ContractId,
                                                         TechnitianId = to.TechnitianId,
                                                         TicketId = to.TicketId,
                                                         StatusCategoryId = ts.CategoryId,//,//d.CategoryId,
                                                         DueOnUtc = to.DueOnUtc,
                                                         DefaultStatusId = ts.DefaultId,//d.DefaultId,
                                                         ClosedOnUtc = to.ClosedOnUtc,
                                                         ResolvedOnUtc = to.ResolvedOnUtc,
                                                         InitialResponseMade = to.InitialResponseMade,
                                                         TicketTypeId = to.TicketTypeId,
                                                         TicketTypeName = tic_type.TicketTypeName
  
                                                     }).OrderByDescending(o =&gt; o.TicketNumber);
  
  
              bool isFiltersHit = false;
  
              if (filters != null)
              {
                  if (tickets != null &amp;&amp; tickets.Count() &gt; 0 &amp;&amp; filters.serviceboard_selectedItems != null &amp;&amp; filters.serviceboard_selectedItems.Count &gt; 0)
                  {
                      isFiltersHit = true;
  
                      tickets = tickets.Where(x =&gt; x.ServiceBoardId != null &amp;&amp; filters.serviceboard_selectedItems.Select(o =&gt; o.serviceBoardId).Contains(x.ServiceBoardId.Value));
  
                  }
  
                  if (tickets != null &amp;&amp; tickets.Count() &gt; 0 &amp;&amp; filters.status_selectedItems != null &amp;&amp; filters.status_selectedItems.Count &gt; 0)
                  {
                      isFiltersHit = true;
  
                      tickets = tickets.Where(x =&gt; x.TicketStatusId != null &amp;&amp; filters.status_selectedItems.Select(o =&gt; o.ticketStatusId).Contains(x.TicketStatusId.Value));
  
                  }
  
                  if (tickets != null &amp;&amp; tickets.Count() &gt; 0 &amp;&amp; filters.type_selectedItems != null &amp;&amp; filters.type_selectedItems.Count &gt; 0)
                  {
                      isFiltersHit = true;
  
                      tickets = tickets.Where(x =&gt; filters.type_selectedItems.Select(o =&gt; o.ticketTypeId).Contains(x.TicketTypeId));
  
                  }
  
                  if (tickets != null &amp;&amp; tickets.Count() &gt; 0 &amp;&amp; filters.technician_selectedItems != null &amp;&amp; filters.technician_selectedItems.Count &gt; 0)
                  {
                      isFiltersHit = true;
  
                      tickets = tickets.Where(x =&gt; x.TechnitianId != null &amp;&amp; filters.technician_selectedItems.Select(o =&gt; o.id).Contains(x.TechnitianId.Value));
  
                  }
  
                  if (tickets != null &amp;&amp; tickets.Count() &gt; 0 &amp;&amp; filters.customer_selectedItems != null &amp;&amp; filters.customer_selectedItems.Count &gt; 0)
                  {
                      isFiltersHit = true;
  
                      tickets = tickets.Where(x =&gt; x.CustomerId != 0 &amp;&amp; filters.customer_selectedItems.Select(o =&gt; o.customerId).Contains(x.CustomerId));
  
                  }
  
                  if (tickets != null &amp;&amp; tickets.Count() &gt; 0 &amp;&amp; filters.priority_selectedItems != null &amp;&amp; filters.priority_selectedItems.Count &gt; 0)
                  {
                      isFiltersHit = true;
  
                      tickets = tickets.Where(x =&gt; x.PriorityId != null &amp;&amp; filters.priority_selectedItems.Select(o =&gt; o.priorityId).Contains(x.PriorityId.Value));
  
                  }
  
                  if (tickets != null &amp;&amp; tickets.Count() &gt; 0 &amp;&amp; filters.contract_selectedItems != null &amp;&amp; filters.contract_selectedItems.Count &gt; 0)
                  {
                      isFiltersHit = true;
  
                      tickets = tickets.Where(x =&gt; x.ContractId != null &amp;&amp; filters.contract_selectedItems.Select(o =&gt; o.contractId).Contains(x.ContractId.Value));
  
                  }
  
                  if (tickets != null &amp;&amp; tickets.Count() &gt; 0 &amp;&amp; filters.source_selectedItems != null &amp;&amp; filters.source_selectedItems.Count &gt; 0)
                  {
                      isFiltersHit = true;
  
                      tickets = tickets.Where(x =&gt; x.SourceId != 0 &amp;&amp; filters.source_selectedItems.Select(o =&gt; o.item_id).Contains(x.SourceId));
  
                  }
  
              }
  
  
              if (take.HasValue)
                  return tickets.Take(take.Value);
  
              return tickets;
          }
  </code></pre>