EntityFramework LINQ query count fails but query returns result. How to optimize LINQ query?

Question

I have the below LINQ query that performs a self-left-outer-join. The querys looks a little complex but is simply doing a self join on itself(purpose if to join each record with the record for it previous business day) and then doing some parameterized filtering.

var newBreakThreshold = decimal.Parse(WebConfigurationManager.AppSettings["NewBreakThreshold"]);
using (var dbContext = new NavFoToBoCompareDbContext())
{
    var query = from current in dbContext.NAVSummaries
                let currentWD = SqlFunctions.DatePart("dw", current.ValueDate)
                let currentPD = DbFunctions.AddDays(current.ValueDate, currentWD == 2 ? -3 : currentWD == 1 ? -2 : -1).Value
                join previous in dbContext.NAVSummaries
                on new { current.Portfolio, PD = currentPD }
                equals new { previous.Portfolio, PD = previous.ValueDate }
                into previousGroup
                from previous in previousGroup.DefaultIfEmpty() // LEFT OUTER JOIN
                select new { outer = current, inner = previous };

    if (dateStart.HasValue)
        query = query.Where(e => e.outer.ValueDate >= dateStart.Value);
    if (dateEnd.HasValue)
        query = query.Where(e => e.outer.ValueDate <= dateEnd.Value);
    if (!portfolio.Equals("ALL", StringComparison.OrdinalIgnoreCase))
        query = query.Where(e => e.outer.Portfolio.Equals(portfolio, StringComparison.OrdinalIgnoreCase));
    if (!owner.Equals("ALL", StringComparison.OrdinalIgnoreCase))
        query = query.Where(e => e.outer.PortfolioOwner.Equals(owner, StringComparison.OrdinalIgnoreCase));
    if (status != 0)
        query = query.Where(e => e.outer.Statuses.Any(s => s.StatusId == status));

    var query2 = query.Select(s => new
                {
                    BackOfficeNAV = s.outer.BackOfficeNAV,
                    FrontOfficeNAV = s.outer.FrontOfficeNAV,
                    Threshold = s.outer.Threshold,
                    ExtractId = s.outer.ExtractId,
                    ExtractStatus = s.outer.ExtractStatus,
                    PortfolioOwner = s.outer.PortfolioOwner,
                    DateTimeModified = s.outer.DateTimeModified,
                    MostCorrectNAV = s.outer.MostCorrectNAV,
                    Comments = s.outer.Comments,
                    Statuses = s.outer.Statuses,
                    Extracts = s.outer.Extracts,
                    Portfolio = s.outer.Portfolio,
                    ValueDate = s.outer.ValueDate,
                    DifferencePercent = s.outer.DifferencePercent,
                    DayOverDayChange = s.outer.DifferencePercent - s.inner.DifferencePercent,
                    IsChange = s.inner.DifferencePercent != s.outer.DifferencePercent,

                    PreviousValueDate = s.inner.ValueDate,
                    PreviousDifferencePercent = s.inner.DifferencePercent
                });


    query2 = query2.Where(r => "NEW".Equals(breakOption, StringComparison.OrdinalIgnoreCase) ?
                                                ((r.DifferencePercent > r.Threshold) && r.IsChange && r.DayOverDayChange > newBreakThreshold) :
                                "OLD".Equals(breakOption, StringComparison.OrdinalIgnoreCase) ? (r.DifferencePercent > r.Threshold) :
                                "ALL".Equals(breakOption, StringComparison.OrdinalIgnoreCase));

    var resultCount = query2.Count();
}

The query is used in two places. In one method it used for computing the count required for pagination. In another method it is used for getting the actual results from the database. The implementation for getting the actual results for a bigger result set executes successfully, whereas the the Count() query fails with a Timeout exception. Note:Both implementations are exactly the same.

Can someone help me in optimizing this query as well. Thanks in advance.

Answer 1

Not quite sure that's the problem, but at least let try to eliminate the potential effect of the so called Parameter Sniffing Problem by eliminating the dateStart / dateEnd parameters by manually building expression with constant values.

First, a little helper method:

using System;
using System.Linq;
using System.Linq.Expressions;

public static class QueryableUtils
{
    public static IQueryable<T> WhereBetween<T>(this IQueryable<T> source, Expression<Func<T, DateTime>> dateSelector, DateTime? startDate, DateTime? endDate)
    {
        if (startDate == null && endDate == null) return source;
        var startCond = startDate != null ? Expression.GreaterThanOrEqual(dateSelector.Body, Expression.Constant(startDate.Value)) : null;
        var endCond = endDate != null ? Expression.LessThanOrEqual(dateSelector.Body, Expression.Constant(endDate.Value)) : null;
        var predicate = Expression.Lambda<Func<T, bool>>(
            startCond == null ? endCond : endCond == null ? startCond : Expression.AndAlso(startCond, endCond),
            dateSelector.Parameters[0]);
        return source.Where(predicate);
    }
}

Then try the following and see if it helps:

//if (dateStart.HasValue)
//    query = query.Where(e => e.outer.ValueDate >= dateStart.Value);
//if (dateEnd.HasValue)
//    query = query.Where(e => e.outer.ValueDate <= dateEnd.Value);
query = query.WhereBetween(e => e.outer.ValueDate, dateStart, dateEnd);

Answer 2

You can simply use AsParallel() to enable multithreading execution of the linq query. You should then check your tables' indexes to improve performances.