I am trying to optimize this query using EF. This is the query that I had at the beginning.

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

Now I tried optimizing it and have this right now

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

But the problem is that I have an exception thrown that it cannot translate the query. Do you have any solution to my exception? Or maybe another approach to the query?

This is the exception:

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. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Translation of method 'Signumid.ProductProvisioning.ProductProvisioningRepository.CalculateDifferenceBetweenEntriesAndConsummations' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

This is what CalculateDifferenceBetweenEntriesAndConsummations does:

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

CodePudding user response：

You're mixing work in the database and in the application querying the database.

Things before .ToList[Async()] happen in the database and things after happen in the application.

Here are some options:

1. Create a database function CalculateDifferenceBetweenEntriesAndConsummations in the database and use it in the linq query
2. Don't use a function and do the calculations inside Where - this will work if the calcs are simple enough to be translated to SQL. The snippet having .Result when calling the function suggest that it's async and that makes things complicated, but maybe it doesn't have to be async.
3. Fetch all records from the database and apply CalculateDifferenceBetweenEntriesAndConsummations afterwards - what you had originally
4. Write the query in SQL and use that instead of using EF.

Side points:

return Task.FromResult(_dbContext.signumid_organization
            .Where(Select(x => x.Admin).ToList());

should most likely be

await _dbContext.signumid_organization
            .Where(Select(x => x.Admin).ToListAsync();

The same applies to _dbContext.signumid_organization.ToListAsync().Result. which should be `(await _dbContext.signumid_organization.ToListAsync()).

UPDATE after the CalculateDifferenceBetweenEntriesAndConsummations was posted.

These 3 queries can be combined into one in SQL and it could look something like this:

SELECT 
 name
FROM signumid_organization org
JOIN signumid_credit_operation co on co.OrganizationId = org.Id
WHERE co.OperationType in (number_for_Purchase, number_for_Consummation)
GROUP BY org.Id
HAVING  
   (  SUM(CASE co.OperationType = number_for_Purchase THEN Amount ELSE 0 END)
    - SUM(CASE co.OperationType = number_for_Consummation THEN Amount ELSE 0 END)
   > threshold -- or '<=', I'm not sure

You could finalize this query and then executed this from your code or finsalize this query and tran translate it to LINQ.

CodePudding user response：

Well, everything can be done on the server:

var result = _dbContext.signumid_credit_operation
    .GroupBy(x => new { x.OrganizationId, organization.Admin })
    .Select(g => new
    {
        g.Key.OrganizationId,
        g.Key.Admin,
        Difference = g.Sum(x => x.OperationType == OperationType.Purchase ? x.Amount : 0) 
                    - g.Sum(x => x.OperationType == OperationType.Consummation ? x.Amount : 0)
    })
    .Where(x => x.Difference <= 100 && !string.IsNullOrEmpty(x.Admin))
    .Select(x => x.Admin)
    .ToListAsync();

return result;