I have an asynchronous method in C# that retrieves a list of users with various nested includes and filters. The method works fine when I don’t pass the arbsResponse parameter, but I encounter an exception when I do.

Here’s the method:

public async Task<List<User>> GetActiveNonDeletedUsersWithValidBookmakersAndFilters(List<ArbsResponse> arbsResponse, List<ValuebetArbsResponse> valuebetResponses)
{
    var users = await _context.Users
        .Where(u => u.TaskIsActive && !u.Deleted)
        .Include(u => u.UsersBookmakers.Where(ub => !ub.Deleted))
            .ThenInclude(ub => ub.UsersFilters.Where(uf => uf.IsActive && !uf.Deleted))
                .ThenInclude(uf => uf.UsersFilterCountries.Where(ufc => !ufc.Deleted))
        .Include(u => u.UsersBookmakers.Where(ub => !ub.Deleted))
            .ThenInclude(ub => ub.UsersFilters.Where(uf => uf.IsActive && !uf.Deleted))
                .ThenInclude(uf => uf.UsersFilterLeagues.Where(ufl => !ufl.Deleted))
        .Include(u => u.UsersBookmakers.Where(ub => !ub.Deleted))
            .ThenInclude(ub => ub.UsersFilters.Where(uf => uf.IsActive && !uf.Deleted))
                .ThenInclude(uf => uf.UsersFilterStrategyTypes.Where(ufst => !ufst.Deleted))
        .Include(u => u.UsersBookmakers.Where(ub => !ub.Deleted))
            .ThenInclude(ub => ub.UsersFilters.Where(uf => uf.IsActive && !uf.Deleted))
                .ThenInclude(uf => uf.UsersFilterBetTypes.Where(ufb => !ufb.Deleted))
        .Include(u => u.UsersBookmakers.Where(ub => !ub.Deleted))
            .ThenInclude(ub => ub.UsersFilters.Where(uf => uf.IsActive && !uf.Deleted))
                .ThenInclude(uf => uf.UsersFilterBetTimings.Where(ufbt => !ufbt.Deleted))
            .Where(u => u.UsersBookmakers.Any(ub => ub.UsersFilters.Any(uf => uf.IsActive && !uf.Deleted &&
    (
        arbsResponse.Any(arb =>
            (arb.MinKoef >= uf.MinimalKoef && arb.MaxKoef <= uf.MaximalKoef) 
        ) ||
        valuebetResponses.Any(valuebet =>
            (valuebet.MinKoef >= uf.MinimalKoef && valuebet.MaxKoef <= uf.MaximalKoef) 
        ))
    ))).ToListAsync();

    return users;
}

When I call this method with the arbsResponse parameter, I get the following exception:

System.InvalidOperationException: 'The LINQ expression 'arb => arb.MinKoef >= EntityShaperExpression: 
    Core.Entities.UsersFilter
    ValueBufferExpression: 
        ProjectionBindingExpression: Inner
    IsNullable: False
.MinimalKoef && arb.MaxKoef <= EntityShaperExpression: 
    Core.Entities.UsersFilter
    ValueBufferExpression: 
        ProjectionBindingExpression: Inner
    IsNullable: False
.MaximalKoef' could not be translated. 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.'

It seems like the LINQ query can’t be translated into a form that Entity Framework Core can execute on the database side.

When I don’t pass the arbsResponse parameter, the method executes without any issues. Could anyone help me understand why this exception is occurring and how I can modify the query so that it can be translated and executed properly?

Additional Information

Entity Framework Core version: 6.0.32

.NET version:6.0

Clarification:

I would like to clarify that the initial requirement was to perform all operations on the database side. When these operations are performed in-memory, everything works perfectly.

Any advice or suggestions would be greatly appreciated. Thank you!

I tried removing the arbsResponse parameter from the query, and the method executed without any issues. This led me to believe that the problem lies within the part of the query that involves arbsResponse. I also attempted to simplify the query by reducing the number of nested Include and ThenInclude clauses, but the error persisted when arbsResponse was part of the query.

I expected the query to execute and return a list of users who have active, non-deleted tasks and meet the specified filtering criteria based on arbsResponse and valuebetResponses.