如何优化EF查询？解决自定义方法导致LINQ无法翻译的问题

问题背景 #

尝试使用Entity Framework优化查询时，将自定义方法CalculateDifferenceBetweenEntriesAndConsummations直接写入LINQ的Where条件后，抛出LINQ表达式无法转换的异常。

初始查询代码 #

var result = new List<string>();

_dbContext.signumid_organization.ToListAsync().Result.ForEach(organization =>
{
    if (CalculateDifferenceBetweenEntriesAndConsummations(null, organization.Id).Result > threshold)
    {
        return;
    }

    if (!string.IsNullOrEmpty(organization.Admin))
    {
        result.Add(organization.Admin);
    }
});

return Task.FromResult(result);

优化后抛出异常的代码 #

return Task.FromResult(_dbContext.signumid_organization
    .Where(organization => !string.IsNullOrEmpty(organization.Admin) &&
                            CalculateDifferenceBetweenEntriesAndConsummations(null, organization.Id).Result <=
                            threshold).Select(x => x.Admin).ToList());

异常信息 #

System.InvalidOperationException: The LINQ expression 'DbSet()
.Where(o => !(string.IsNullOrEmpty(o.Admin)) && ProductProvisioningRepository.CalculateDifferenceBetweenEntriesAndConsummations(
phoneNumber: null,
organizationId: (int?)o.Id).Result <= __p_0)' could not be translated. Additional information: Translation of method 'Signumid.ProductProvisioning.ProductProvisioningRepository.CalculateDifferenceBetweenEntriesAndConsummations' failed. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

自定义方法实现 #

if (organizationId != null)
{
    return await _dbContext.signumid_credit_operation
                .Where(x => x.OrganizationId == organizationId && x.OperationType == OperationType.Purchase)
                .SumAsync(x => x.Amount)
            - await _dbContext.signumid_credit_operation
                .Where(x => x.OrganizationId == organizationId && x.OperationType == OperationType.Consummation)
                .SumAsync(x => x.Amount);
}

异常原因 #

Entity Framework无法将自定义的C#方法翻译成对应的SQL语句——EF仅能识别内置LINQ方法、可映射的表达式树，自定义方法不在其翻译规则范围内。

解决方案 #

方案1：将计算逻辑内联到LINQ查询（推荐，全SQL端执行） #

把自定义方法中的求和逻辑直接写入LINQ查询，让EF可以完整转换成SQL，全程在数据库端执行，性能最优：

return await _dbContext.signumid_organization
    .Where(o => !string.IsNullOrEmpty(o.Admin))
    .Select(o => new 
    {
        o.Admin,
        // 处理无匹配记录时Sum返回null的情况，转为0
        PurchaseSum = _dbContext.signumid_credit_operation
            .Where(co => co.OrganizationId == o.Id && co.OperationType == OperationType.Purchase)
            .Sum(co => (decimal?)co.Amount) ?? 0,
        ConsummationSum = _dbContext.signumid_credit_operation
            .Where(co => co.OrganizationId == o.Id && co.OperationType == OperationType.Consummation)
            .Sum(co => (decimal?)co.Amount) ?? 0
    })
    .Where(x => (x.PurchaseSum - x.ConsummationSum) <= threshold)
    .Select(x => x.Admin)
    .ToListAsync();

方案2：客户端部分执行（适合数据量小的场景） #

先将符合Admin非空条件的组织数据拉取到客户端内存，再逐个调用自定义方法计算差值。注意：如果组织数量大，会发起N次数据库查询，性能较差：

// 先拉取符合Admin条件的组织到内存
var organizations = await _dbContext.signumid_organization
    .Where(o => !string.IsNullOrEmpty(o.Admin))
    .ToListAsync();

var result = new List<string>();
foreach (var org in organizations)
{
    var difference = await CalculateDifferenceBetweenEntriesAndConsummations(null, org.Id);
    if (difference <= threshold)
    {
        result.Add(org.Admin);
    }
}

return result;

方案3：重构为可翻译的表达式树（复用逻辑场景） #

如果需要复用差值计算逻辑，可以将其封装为表达式树，让EF能够识别并翻译：

// 定义可复用的表达式树
public Expression<Func<signumid_organization, decimal>> GetDifferenceExpression()
{
    return o => 
        (_dbContext.signumid_credit_operation
            .Where(co => co.OrganizationId == o.Id && co.OperationType == OperationType.Purchase)
            .Sum(co => (decimal?)co.Amount) ?? 0)
        - (_dbContext.signumid_credit_operation
            .Where(co => co.OrganizationId == o.Id && co.OperationType == OperationType.Consummation)
            .Sum(co => (decimal?)co.Amount) ?? 0);
}

查询时使用：

var differenceExpr = GetDifferenceExpression();
return await _dbContext.signumid_organization
    .Where(o => !string.IsNullOrEmpty(o.Admin) && differenceExpr.Invoke(o) <= threshold)
    .Select(o => o.Admin)
    .ToListAsync();

注意：部分EF版本对Invoke的支持有限，优先推荐方案1。

内容的提问来源于stack exchange，提问作者MatejDodevski