EF Core中LINQ表达式无法翻译的问题修复求助

在EF Core中实现数据过滤功能时，出现The LINQ expression could be translated错误，根源是在LINQ查询的Where和Select中调用了自定义的GetDataLimit、GetDataWarning方法。EF Core无法将C#自定义方法直接转换为对应的SQL语句，必须将方法内的逻辑直接嵌入LINQ表达式，或者通过表达式树封装逻辑实现复用。

解决方案1：直接内联逻辑（快速修复） #

把GetDataLimit和GetDataWarning的逻辑直接写到LINQ查询中，替代方法调用：

修改PrepareDataUsageFilter方法 #

private IQueryable<AppDataUsage> PrepareDataUsageFilter(IQueryable<AppDataUsage> dataUsageReports, DataUsageFilter dataUsageFilter, List<AppDataUsageSettings> reportSettings, AppDataUsageSettings defaultSetting, int minData, int MaxData)
{
    if (dataUsageFilter == DataUsageFilter.Custom)
    {
        return dataUsageReports.Where(a => a.TotalMobileDataUsage >= minData && a.TotalMobileDataUsage <= MaxData);
    }
    else if (dataUsageFilter == DataUsageFilter.Exceeded)
    {
        return dataUsageReports.Where(a => a.TotalMobileDataUsage > 
            reportSettings
                .Where(r => r.SettingMappings.Any(x => x.ObjectId == a.PolicyId || x.ObjectId == a.GroupId))
                .Select(r => r.DataLimit)
                .DefaultIfEmpty(defaultSetting.DataLimit)
                .FirstOrDefault());
    }
    else if (dataUsageFilter == DataUsageFilter.Approaching)
    {
        return dataUsageReports.Where(a => 
            a.TotalMobileDataUsage >= reportSettings
                .Where(r => r.SettingMappings.Any(x => x.ObjectId == a.PolicyId || x.ObjectId == a.GroupId))
                .Select(r => r.DataLimit)
                .DefaultIfEmpty(defaultSetting.DataLimit)
                .FirstOrDefault()
            && a.TotalMobileDataUsage < reportSettings
                .Where(r => r.SettingMappings.Any(x => x.ObjectId == a.PolicyId || x.ObjectId == a.GroupId))
                .Select(r => r.DataWarning)
                .DefaultIfEmpty(defaultSetting.DataWarning)
                .FirstOrDefault());
    }

    return dataUsageReports;
}

修改gridData的查询部分 #

var gridData = await dataUsageReports.OrderByDescending(a => a.TotalMobileDataUsage).Skip(dto.SkipCount).Take(dto.MaxResultCount).Select(d => new AppDataUsageWithTotalCountDto
{
    DeviceUniqueIdentifier = d.DeviceUniqueIdentifier,
    MobileDataUsage = d.TotalMobileDataUsage,
    WifiDataUsage = d.TotalWifiUsage,
    TotalCount = totalCount,
    DataLimit = reportSettings
        .Where(r => r.SettingMappings.Any(x => x.ObjectId == d.PolicyId || x.ObjectId == d.GroupId))
        .Select(r => r.DataLimit)
        .DefaultIfEmpty(defaultSetting.DataLimit)
        .FirstOrDefault(),
    DataWarning = reportSettings
        .Where(r => r.SettingMappings.Any(x => x.ObjectId == d.PolicyId || x.ObjectId == d.GroupId))
        .Select(r => r.DataWarning)
        .DefaultIfEmpty(defaultSetting.DataWarning)
        .FirstOrDefault()
}).ToListAsync();

解决方案2：用表达式树封装逻辑（适合复用场景） #

如果需要在多处复用GetDataLimit和GetDataWarning的逻辑，可以将它们封装为表达式树，让EF Core能够解析转换：

定义表达式树生成方法 #

private static Expression<Func<AppDataUsage, byte>> GetDataLimitExpression(List<AppDataUsageSettings> reportSettings, AppDataUsageSettings defaultSetting)
{
    return a => reportSettings
        .Where(r => r.SettingMappings.Any(x => x.ObjectId == a.PolicyId || x.ObjectId == a.GroupId))
        .Select(r => r.DataLimit)
        .DefaultIfEmpty(defaultSetting.DataLimit)
        .FirstOrDefault();
}

private static Expression<Func<AppDataUsage, int>> GetDataWarningExpression(List<AppDataUsageSettings> reportSettings, AppDataUsageSettings defaultSetting)
{
    return a => reportSettings
        .Where(r => r.SettingMappings.Any(x => x.ObjectId == a.PolicyId || x.ObjectId == a.GroupId))
        .Select(r => r.DataWarning)
        .DefaultIfEmpty(defaultSetting.DataWarning)
        .FirstOrDefault();
}

修改PrepareDataUsageFilter方法使用表达式树 #

private IQueryable<AppDataUsage> PrepareDataUsageFilter(IQueryable<AppDataUsage> dataUsageReports, DataUsageFilter dataUsageFilter, List<AppDataUsageSettings> reportSettings, AppDataUsageSettings defaultSetting, int minData, int MaxData)
{
    var dataLimitExpr = GetDataLimitExpression(reportSettings, defaultSetting);
    var dataWarningExpr = GetDataWarningExpression(reportSettings, defaultSetting);

    if (dataUsageFilter == DataUsageFilter.Custom)
    {
        return dataUsageReports.Where(a => a.TotalMobileDataUsage >= minData && a.TotalMobileDataUsage <= MaxData);
    }
    else if (dataUsageFilter == DataUsageFilter.Exceeded)
    {
        var param = Expression.Parameter(typeof(AppDataUsage), "a");
        var exceededExpr = Expression.Lambda<Func<AppDataUsage, bool>>(
            Expression.GreaterThan(
                Expression.PropertyOrField(param, nameof(AppDataUsage.TotalMobileDataUsage)),
                Expression.Invoke(dataLimitExpr, param)
            ),
            param
        );
        return dataUsageReports.Where(exceededExpr);
    }
    else if (dataUsageFilter == DataUsageFilter.Approaching)
    {
        var param = Expression.Parameter(typeof(AppDataUsage), "a");
        var approachingExpr = Expression.Lambda<Func<AppDataUsage, bool>>(
            Expression.AndAlso(
                Expression.GreaterThanOrEqual(
                    Expression.PropertyOrField(param, nameof(AppDataUsage.TotalMobileDataUsage)),
                    Expression.Invoke(dataLimitExpr, param)
                ),
                Expression.LessThan(
                    Expression.PropertyOrField(param, nameof(AppDataUsage.TotalMobileDataUsage)),
                    Expression.Invoke(dataWarningExpr, param)
                )
            ),
            param
        );
        return dataUsageReports.Where(approachingExpr);
    }

    return dataUsageReports;
}

在gridData查询中复用表达式树 #

如果需要在Select中复用逻辑，可以先将表达式树编译为委托，再在内存中处理数据（注意数据量较大时的性能问题）：

var dataLimitFunc = GetDataLimitExpression(reportSettings, defaultSetting).Compile();
var dataWarningFunc = GetDataWarningExpression(reportSettings, defaultSetting).Compile();

var gridData = await dataUsageReports.OrderByDescending(a => a.TotalMobileDataUsage).Skip(dto.SkipCount).Take(dto.MaxResultCount)
    .AsEnumerable()
    .Select(d => new AppDataUsageWithTotalCountDto
    {
        DeviceUniqueIdentifier = d.DeviceUniqueIdentifier,
        MobileDataUsage = d.TotalMobileDataUsage,
        WifiDataUsage = d.TotalWifiUsage,
        TotalCount = totalCount,
        DataLimit = dataLimitFunc(d),
        DataWarning = dataWarningFunc(d)
    }).ToListAsync();

注意：AsEnumerable()会将数据先加载到内存再处理，适合小数据量场景；数据量大时建议直接内联逻辑，避免内存压力。

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