异步方法中LINQ查询报错：无法翻译LINQ表达式

问题 #

我有一个C#异步方法，用于获取包含各类嵌套Include和过滤条件的用户列表。当不传入arbsResponse参数时方法运行正常，但传入该参数时会触发异常。

方法代码 #

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

触发的异常 #

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'.'

补充信息 #

• Entity Framework Core版本：6.0.32
• .NET版本：6.0
• 初始需求是所有操作在数据库端执行，内存中执行时一切正常
• 移除arbsResponse参数后方法正常，简化嵌套Include子句后，只要包含arbsResponse仍会报错
• 预期结果：返回具有活跃未删除任务、符合arbsResponse和valuebetResponses过滤条件的用户列表

解决方案 #

异常原因 #

EF Core无法将内存集合（arbsResponse、valuebetResponses）与数据库实体字段的双向比较逻辑转换为SQL。具体来说，arbsResponse.Any(arb => arb.MinKoef >= uf.MinimalKoef && arb.MaxKoef <= uf.MaximalKoef)这种用内存集合元素匹配数据库字段的写法，EF Core 6.0不支持此类反向集合比较的SQL转换。

修改方案 #

通过提取内存集合的关键数据，将逻辑转换为EF Core可识别的形式，确保所有过滤在数据库端执行：

修改后的代码

public async Task<List<User>> GetActiveNonDeletedUsersWithValidBookmakersAndFilters(List<ArbsResponse> arbsResponse, List<ValuebetArbsResponse> valuebetResponses)
{
    // 提取内存集合中的系数范围数据，简化EF Core转换逻辑
    var arbKoefRanges = arbsResponse.Select(a => new { a.MinKoef, a.MaxKoef }).ToList();
    var valuebetKoefRanges = valuebetResponses.Select(v => new { v.MinKoef, v.MaxKoef }).ToList();

    var users = await _context.Users
        .Where(u => u.TaskIsActive && !u.Deleted)
        // 简化Include写法：首次过滤UsersBookmakers后，后续Include自动继承过滤条件
        .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)
            .ThenInclude(ub => ub.UsersFilters)
                .ThenInclude(uf => uf.UsersFilterLeagues.Where(ufl => !ufl.Deleted))
        .Include(u => u.UsersBookmakers)
            .ThenInclude(ub => ub.UsersFilters)
                .ThenInclude(uf => uf.UsersFilterStrategyTypes.Where(ufst => !ufst.Deleted))
        .Include(u => u.UsersBookmakers)
            .ThenInclude(ub => ub.UsersFilters)
                .ThenInclude(uf => uf.UsersFilterBetTypes.Where(ufb => !ufb.Deleted))
        .Include(u => u.UsersBookmakers)
            .ThenInclude(ub => ub.UsersFilters)
                .ThenInclude(uf => uf.UsersFilterBetTimings.Where(ufbt => !ufbt.Deleted))
        .Where(u => u.UsersBookmakers.Any(ub => ub.UsersFilters.Any(uf => 
            uf.IsActive && !uf.Deleted &&
            (
                // 转换为EF Core可翻译的范围匹配逻辑
                arbKoefRanges.Any(range => range.MinKoef >= uf.MinimalKoef && range.MaxKoef <= uf.MaximalKoef) ||
                valuebetKoefRanges.Any(range => range.MinKoef >= uf.MinimalKoef && range.MaxKoef <= uf.MaximalKoef)
            )
        )))
        .ToListAsync();

    return users;
}

关键优化点

1. 提取内存集合关键数据：将复杂的响应对象转换为仅包含系数范围的简单匿名类型列表，降低EF Core的转换复杂度。
2. 简化Include语句：避免重复过滤同一个导航属性，首次过滤后后续Include会自动继承过滤规则，减少冗余代码。
3. 保持数据库端执行：修改后的逻辑可被EF Core完整转换为SQL，所有过滤操作在数据库端完成，符合初始需求。

若上述方案仍存在转换问题（如EF Core 6.0对匿名类型集合支持有限），可进一步将范围数据转换为元组列表，或使用原生SQL处理范围匹配，但优先推荐上述方案。

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