LINQ Performance Issue on only a few hundred records

Question

in the following code i've commented on the line that SLOWS my page right down. I did some speed test to reveal the CONTAINS LINQ expression is the problem.

Does anyone know how to change this one line to be more efficient using something else instead. I'm also curious as to why its so slow.

Any ideas (thanks in advance):

    var allWaste = _securityRepository.FindAllWaste(userId, SystemType.W);
    var allWasteIndicatorItems = _securityRepository.FindAllWasteIndicatorItems();

    // First get all WASTE RECORDS
    var searchResults = (from s in allWaste
                         join x in allWasteIndicatorItems on s.WasteId equals x.WasteId
                         where (s.Description.Contains(searchText)
                         && s.Site.SiteDescription.EndsWith(searchTextSite)
                         && (s.CollectedDate >= startDate && s.CollectedDate <= endDate))
                         && x.EWC.EndsWith(searchTextEWC)
                         select s).Distinct();

    var results = searchResults.AsEnumerable();

    if (hazardous != "-1") 
    {
        // User has requested to filter on Hazardous or Non Hazardous only rather than Show All
        var HazardousBoolFiltered = (from we in _db.WasteIndicatorItems
        .Join(_db.WasteIndicators, wii => wii.WasteIndicatorId, wi => wi.WasteIndicatorId, (wii, wi) => new { wasteid = wii.WasteId, wasteindicatorid = wii.WasteIndicatorId, hazardtypeid = wi.HazardTypeId })
        .Join(_db.HazardTypes, w => w.hazardtypeid, h => h.HazardTypeId, (w, h) => new { wasteid = w.wasteid, hazardous = h.Hazardous })
        .GroupBy(g => new { g.wasteid, g.hazardous })
        .Where(g => g.Key.hazardous == true && g.Count() >= 1)
                                     select we).AsEnumerable(); // THIS IS FAST

        // Now join the 2 object to eliminate all the keys that do not apply
        if (bHazardous)
            results = (from r in results join x in HazardousBoolFiltered on r.WasteId equals x.Key.wasteid select r).AsEnumerable(); //This is FAST
        else
            results = (from r in results.Where(x => !HazardousBoolFiltered
                .Select(y => y.Key.wasteid).Contains(x.WasteId)) select r).AsEnumerable(); // This is DOG SLOW 10-15 seconds !--- THIS IS SLOWING EXECUTION by 10 times --!


    }


    return results.AsQueryable();

Answer 1

I suggest using a logging / tracing framework like smart inspect or log4net combined with a debug text writer. http://www.codesprouts.com/post/View-LINQ-To-SQL-Statements-Using-A-Debug-TextWriter.aspx

another possibility is to use the sql server profiler and see what sql linq2sql produces.

also a very nice way is to use the mvc mini profiler in combination with the Profiled DB Connection and the SqlFormatters.SqlServerFormatter.