Merge two Entity Framework queries into one-CodePudding

I have two queries in one method. The difference between them is that when organizationId passed in the method is not null you should look up by organizationId. When the organizationId is null in the method, then retrieve all operations by phoneNumber and with organizationId null. See the following:

public async Task<decimal> CalculateDifferenceBetweenEntriesAndConsummations(string? phoneNumber,
    int? organizationId)
{
    if (organizationId != null)
    {
        return await _dbContext.signumid_credit_operation
                               .Where(x => x.OrganizationId == organizationId && 
                                           (x.OperationType == OperationType.Purchase || x.OperationType == OperationType.Refund))
                               .SumAsync(x => x.Amount)
               - await _dbContext.signumid_credit_operation
                                 .Where(x => x.OrganizationId == organizationId && x.OperationType == OperationType.Consummation)
                                 .SumAsync(x => x.Amount);
    }

    return await _dbContext.signumid_credit_operation
                           .Where(x => x.PhoneNumber == phoneNumber && (x.OperationType == OperationType.Purchase || x.OperationType == OperationType.Refund) && x.OrganizationId == null)
                           .SumAsync(x => x.Amount)
           - await _dbContext.signumid_credit_operation
                             .Where(x => x.PhoneNumber == phoneNumber && x.OperationType == OperationType.Consummation && x.OrganizationId == null)
                             .SumAsync(x => x.Amount);
}

I would like to have only one return in this method combining the queries and still doing the same job.

Is that possible?

CodePudding user response：

I would think this should work:

.Where(x => ((organizationId != null &&
              x.OrganizationId == organizationId) ||
             (organizationId == null &&
              x.OrganizationId == null &&
              x.PhoneNumber == phoneNumber)) &&

Actually, I think that should simplify to this:

.Where(x => x.OrganizationId == organizationId &&
            (organizationId != null || x.PhoneNumber == phoneNumber) &&

CodePudding user response：

Put the common predicate in a variable:

public async Task<decimal> CalculateDifferenceBetweenEntriesAndConsummations(string? phoneNumber,
    int? organizationId)
{

    var baseQuery = _dbContext.signumid_credit_operation;

    if (organizationId != null)
    {
        baseQuery = baseQuery.Where(x => x.OrganizationId == organizationId);
    }
    else
    {
        baseQuery = baseQuery.Where(x => x.PhoneNumber == phoneNumber && x.OrganizationId == null);
    }

    return await baseQuery.Where(x=> x.OperationType == OperationType.Purchase || x.OperationType == OperationType.Refund)
                          .SumAsync(x => x.Amount) - 
           await baseQuery.Where(x=> x.OperationType == OperationType.Consummation)
                          .SumAsync(x => x.Amount);
}