EF Core 动态 lambda 子查询不起作用

Question

我对一些动态创建的IQueriable<T>有问题，后来用作另一个查询的子查询。我将尝试解释：

我使用一种返回IQueryable的方法：

private IQueryable<ProcessValueBase> GetQuery(string period, DeviceModel device)
{
var predicatePv = PredicateBuilder.New<ProcessValueModel>()
.And(pv => pv.TagSettings.DeviceId == device.Id)
.And(GetSetpointCommonExpression<ProcessValueModel>(GetSetpointTagTypeId(device.DeviceTypeId)));
var predicateNv = PredicateBuilder.New<NormalizedLogValueModel>()
.And(pv => pv.TagSettings.DeviceId == device.Id)
.And(GetSetpointCommonExpression<NormalizedLogValueModel>(GetSetpointTagTypeId(device.DeviceTypeId)));
var utcTime = DateTime.Now.ToUniversalTime();
switch (period)
{
case TimePeriodType.Current:
return dbContext.ProcessValues.Where(predicatePv);
case TimePeriodType.Day:
var startOfDay = utcTime.StartOfDay();
predicateNv = predicateNv.And(v => v.Timestamp >= startOfDay && v.Timestamp < startOfDay.AddDays(1));
break;
case TimePeriodType.Week:
var startOfWeek = utcTime.FirstDayOfWeek();
predicateNv = predicateNv.And(v => v.Timestamp >= startOfWeek && v.Timestamp < startOfWeek.AddDays(7));
break;
case TimePeriodType.Month:
var startOfMonth = utcTime.FirstDayOfMonth();
predicateNv = predicateNv.And(v => v.Timestamp >= startOfMonth && v.Timestamp < startOfMonth.AddMonths(1));
break;
default:
break;
}
return dbContext.NormalizedLogValues.Where(predicateNv);
}

然后我在主查询中使用它：

var res = dbContext.Rooms.Select(r => new
{
RoomId = r.Id,
ZoneId = r.ZoneId,
IdealSetpoint = r.Group.Setpoints.First(sp => sp.ClimaticZoneId == dbContext.ClimaticZonesLogs.OrderByDescending(cz => cz.Timestamp).First().ClimaticZoneId).Setpoint,
Devices = r.Devices
.Select(rd => rd.Device)
.Select(d => new
{
Id = d.Id,
Name = d.Name,
//Here is the problem
Setpoint = GetQuery(req.Period, d).Average(t => t.Value)
})
}
).ToList();

我得到的例外是：

The LINQ expression 'SetpointSideViewHandler.GetQuery(
period: __req_Period_0, 
device: NavigationTreeExpression
Value: EntityReference: DeviceModel
Expression: d.Inner)
.Average(t => t.Value)' 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'.

有没有人知道，为什么我会收到此错误？

提前感谢，

朱利安

Answer 1

正如@Neil评论中所说，我们不能在.Select中放置任何函数调用：

您基本上无法在 .选择并期望它在服务器上运行。如果你把你的 GetQuery 代码内联，我敢打赌它会工作

将代码内联，解决问题