的EntityFramework LINQ查询计数失败，但查询返回的结果。如何优化LINQ查询？ [英] EntityFramework LINQ query count fails but query returns result. How to optimize LINQ query?

问题描述

我有下面的LINQ查询执行自左外连接。该querys看起来有点复杂，但简单地做自身自连接（目的如果加入与记录它前一营业日每个记录），然后做一些参数进行过滤。

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.

推荐答案

不太清楚这就是问题所在，但至少让试图消除所谓的潜在影响参数嗅探问题由手工打造的表达与常量值消除 dateStart / dateEnd 参数。

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.

首先，小帮手方式：

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);

这篇关于的EntityFramework LINQ查询计数失败，但查询返回的结果。如何优化LINQ查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！