System.InvalidOperationException: 'The LINQ expression 'u => EntityShaperExpression

Question

I have a List<MeterDataParameter> parameters

When I use

var list = _dbContext.EnergyParameterLogs
    .Where(x => parameters.Any(u => x.DeviceId == u.Serial 
        && x.TimeStampUtc >= u.StartTimeUtc 
        && x.TimeStampUtc < u.EndTimeUtc))
    .ToList();`

I face the Error:

System.InvalidOperationException: 'The LINQ expression 'u => EntityShaperExpression: 
    DataMQTT.Entities.EnergyParameterLog
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.DeviceId == u.Serial && EntityShaperExpression: 
    DataMQTT.Entities.EnergyParameterLog
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.TimeStampUtc >= (DateTime?)u.StartTimeUtc && EntityShaperExpression: 
    DataMQTT.Entities.EnergyParameterLog
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.TimeStampUtc < (DateTime?)u.EndTimeUtc' could not be translated.

Please help to solve this error. Thank you!

Answer 1

EF can't translate this kind of operation with local collection. You can try using temporary table support from linq2db.EntityFrameworkCore and perform join on those conditions. Another approach is to dynamically build the predicate either using expression trees manually or via LINQKit's PredicateBuilder:

var predicate = PredicateBuilder.New<EnergyParameterLog>();
foreach (var parameter in parameters)
{
    predicate = predicate.Or(log => log.DeviceId == parameter.Serial
        && log.TimeStampUtc >= parameter.StartTimeUtc
        && log.TimeStampUtc < parameter.EndTimeUtc);
}
    
var list = _dbContext.EnergyParameterLogs
    .Where(predicate)
    .ToList();