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.
Lexxxa is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
EF Core can’t translate certain LINQ expressions into SQL, especially when it comes to complex nested queries and method calls.
In this case, I think .Where(u => u.UsersBookmakers.Any(ub => .... is probably should be executed separately, more like breaking down the query.
You can try this split (by relying on the client side for the second filtering):
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))
.ToListAsync();
var filteredUsers = users.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)
)
)
))
).ToList();
return filteredUsers;
}
First try if this solves the issue first, but bare in mind that if the dataset is large, loading it into memory for the second filter can have some performance impact.