EntityFramework The number of LINQ queries failed, but the query returns a result. How to optimize LINQ query?

Question

I have the following LINQ query that does self-left-external-join. The queries look a bit complicated, but just make an independent connection to yourself (the goal is to join each record with the record for the previous working day), and then perform 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 request is used in two places. In one method, it was used to calculate the amount needed for pagination. In another method, it is used to obtain actual results from a database. The implementation to obtain actual results for a larger set of results is successful, while the Count () query completes using the Timeout exception. Note. Both implementations are the same.

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

Answer 1

Not quite sure what the problem is, but at least try to eliminate the potential effect of the so-called Sniffing Problem parameter by eliminating dateStart / dateEnd , manually creating an 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 this 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 of linq query execution. Then you should check the indexes of your tables to improve performance.