LinqKit Unable to cast object of type 'System.Linq.Expressions.InstanceMethodCallExpression2' to type 'System.Linq.Expressions.LambdaExpression'

Question

I have a problem with Expressions. I have this lambda LINQ query with Entity Framework Core LINQ Provider:

IQueryable<ProcessValueBase> valuesSubquery;
switch (req.Period)
            {
                case TimePeriodType.Current:
                    valuesSubquery =  dbContext.ProcessValues;
                    break;
                case TimePeriodType.Day:
                case TimePeriodType.Week:
                case TimePeriodType.Month:
                    valuesSubquery = dbContext.NormalizedLogValues;
                    break;
                default:
                    valuesSubquery = dbContext.ProcessValues;
                    break;
            }
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,
                    //Setpoint = GetQuery(rd.Device.Id).Average(t=>t.Value)
                    Setpoint = valuesSubquery.Where(GetQuery(req.Period, d)).Average(t => t.Value)
                })
            }
            ).ToList();

Then I have a function that deals with the predicate dynamically:

private  Expression<Func<ProcessValueBase,bool>> GetQuery(string period, DeviceModel device)
        {
            var predicate = PredicateBuilder.New<ProcessValueBase>();
            predicate = predicate.And(v => v.TagSettings.DeviceId == device.Id);
            predicate = predicate.And(v => v.TagSettings.TagTypeId == GetSetpointTagTypeId(device.DeviceTypeId));
            predicate = predicate.And(v => v.ClimaticZoneId == dbContext.ClimaticZonesLogs.OrderByDescending(cz => cz.Timestamp).First().ClimaticZoneId);

            var utcTime = DateTime.Now.ToUniversalTime();
            switch (period)
            {
                case TimePeriodType.Day:
                    var startOfDay = utcTime.StartOfDay();
                    predicate = predicate.And(v => v.Timestamp >= startOfDay && v.Timestamp < startOfDay.AddDays(1));
                    break;
                case TimePeriodType.Week:
                    var startOfWeek = utcTime.FirstDayOfWeek();
                    predicate = predicate.And(v => v.Timestamp >= startOfWeek && v.Timestamp < startOfWeek.AddDays(7));
                    break;
                case TimePeriodType.Month:
                    var startOfMonth = utcTime.FirstDayOfMonth();
                    predicate = predicate.And(v => v.Timestamp >= startOfMonth && v.Timestamp < startOfMonth.AddMonths(1));
                    break;
                default:
                    break;
            }
            return predicate;
        }

The error that I'm getting is as follows:

The message is:

Unable to cast object of type 'System.Linq.Expressions.InstanceMethodCallExpression2' to type 'System.Linq.Expressions.LambdaExpression'.

Can anyone suggest what am I doing wrong?

Thanks in advance,

Julian

EDIT: As an answer to Svyatoslav Danyliv answer. The exception that it gives me is:

The LINQ expression 'DbSet<NormalizedLogValueModel>()
    .Where(n => (v, device) => v.TagSettings.DeviceId == device.Id && v.TagSettings.TagTypeId == SetpointSideViewHandler.GetSetpointTagTypeId(device.DeviceTypeId) && v.ClimaticZoneId == DbSet<ClimaticZoneLogModel>()
        .OrderByDescending(c0 => c0.Timestamp)
        .Select(c0 => c0.ClimaticZoneId)
        .First() && v.Timestamp >= __startOfDay_0 && v.Timestamp < __AddDays_1
        .Invoke(
            arg1: n, 
            arg2: EntityShaperExpression: 
                EntityType: DeviceModel
                ValueBufferExpression: 
                    ProjectionBindingExpression: Inner
                IsNullable: True
        ))' 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.

When GetQuery(req.Period).Compile()(p,d):

The LINQ expression 'DbSet<NormalizedLogValueModel>()
    .Where(n => Invoke(__Compile_0, n, [RelationalEntityShaperExpression])
    )' 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.

I didn't! I've updated to last version, but the problem persists :( — Julian, Mar 18 '21 at 09:59
I github issue is created: https://github.com/scottksmith95/LINQKit/issues/140 — Julian, Mar 23 '21 at 10:42

Svyatoslav Danyliv · Accepted Answer · 2021-03-23T12:46:53.343

Well answer is complicated, but from my experience it is only one way to do that:

Usage:

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,
            Setpoint = valuesSubquery.Where(p => GetQuery(req.Period).Invoke(dbContext, p, d)).Average(t => t.Value)
        })
    }
    ).ToList();

New function (it could be compiler errors, not tested):

private static Expression<Func<DBIContext, ProcessValueBase, DeviceModel, bool>> GetQuery(string period)
{
    Expression<Func<DBIContext, ProcessValueBase, DeviceModel, bool>> predicateTemplate = (dbContext, v, device) =>
        v.TagSettings.DeviceId == device.Id &&
        v.TagSettings.TagTypeId == GetSetpointTagTypeId(device.DeviceTypeId) &&
        v.ClimaticZoneId == dbContext.ClimaticZonesLogs.OrderByDescending(cz => cz.Timestamp).First().ClimaticZoneId;

    var predicate = PredicateBuilder.New<ProcessValueBase>();

    var utcTime = DateTime.Now.ToUniversalTime();
    switch (period)
    {
        case TimePeriodType.Day:
            var startOfDay = utcTime.StartOfDay();
            predicate = predicate.And(v => v.Timestamp >= startOfDay && v.Timestamp < startOfDay.AddDays(1));
            break;
        case TimePeriodType.Week:
            var startOfWeek = utcTime.FirstDayOfWeek();
            predicate = predicate.And(v => v.Timestamp >= startOfWeek && v.Timestamp < startOfWeek.AddDays(7));
            break;
        case TimePeriodType.Month:
            var startOfMonth = utcTime.FirstDayOfMonth();
            predicate = predicate.And(v => v.Timestamp >= startOfMonth && v.Timestamp < startOfMonth.AddMonths(1));
            break;
        default:
            break;
    }

    Expression<Func<ProcessValueBase, bool>> lambda = predicate;

    var newBody = predicateTemplate.Body;
    newBody = Expression.AndAlso(newBody, ExpressionReplacer.GetBody(lambda, predicateTemplate.Parameters[1]));

    return Expression.Lambda<Func<ProcessValueBase, DeviceModel, bool>>(newBody, predicateTemplate.Parameters);
}

[Expandable(nameof(GetSetpointTagTypeIdImpl))]
private static long GetSetpointTagTypeId(long deviceTypeId)
{
    throw new NotImplementedException();
}

private static Expression<Func<long, long>> GetSetpointTagTypeIdImpl()
{
    return deviceTypeId => deviceTypeId == 180_000 ? 180_002
        : deviceTypeId == 190_000 ? 190_002 : 0;
}

And helper class:

public class ExpressionReplacer : ExpressionVisitor
{
    readonly IDictionary<Expression, Expression> _replaceMap;

    public ExpressionReplacer(IDictionary<Expression, Expression> replaceMap)
    {
        _replaceMap = replaceMap ?? throw new ArgumentNullException(nameof(replaceMap));
    }

    public override Expression Visit(Expression exp)
    {
        if (exp != null && _replaceMap.TryGetValue(exp, out var replacement))
            return replacement;
        return base.Visit(exp);
    }

    public static Expression Replace(Expression expr, Expression toReplace, Expression toExpr)
    {
        return new ExpressionReplacer(new Dictionary<Expression, Expression> {{toReplace, toExpr}}).Visit(expr);
    }

    public static Expression Replace(Expression expr, IDictionary<Expression, Expression> replaceMap)
    {
        return new ExpressionReplacer(replaceMap).Visit(expr);
    }
    
    public static Expression GetBody(LambdaExpression lambda, params Expression[] toReplace)
    {
        if (lambda.Parameters.Count != toReplace.Length)
            throw new InvalidOperationException();

        return new ExpressionReplacer(Enumerable.Range(0, lambda.Parameters.Count)
            .ToDictionary(i => (Expression) lambda.Parameters[i], i => toReplace[i])).Visit(lambda.Body);
    }
}

Hello, I've tried this approach, and I had an error `The LINQ expression .... ))' could not be translated. Either rewrite the query in a form that can be translated` — Julian, Mar 18 '21 at 13:39
Try instead of `GetQuery(req.Period).Invoke(p, d)` - `GetQuery(req.Period).Compile()(p, d)` — Svyatoslav Danyliv, Mar 18 '21 at 14:35
The same as the one with `GetQuery(req.Period).Invoke(p, d)` — Julian, Mar 18 '21 at 14:55
Looks like a LINQKit bug. If you prepare github issue with this code, I'll try to fix. — Svyatoslav Danyliv, Mar 18 '21 at 14:57
Let's try again. I have added additional parameter `MyDbContext`, I do not know type of your context. `GetSetpointTagTypeId` also needs expanding. Also do not change static to non-static methods. — Svyatoslav Danyliv, Mar 23 '21 at 11:32
@Julian, I have tested it works. No LINQKit bugs, probably you have changed static to non-static - it's important. Updated only parameter index. — Svyatoslav Danyliv, Mar 23 '21 at 11:50
Like written above, it gives compiler error on `return Expression.Lambda>(newBody, predicateTemplate.Parameters);` - `Cannot implicitly convert type Expression> to Expression>` All code is Copy/Paste from your answer — Julian, Mar 23 '21 at 12:30
When I correct the issue it again gives me an error that I have to rewrite the query — Julian, Mar 23 '21 at 12:32
Corrected types. Yes, it is another problem )) Your query is totally weird for LINQ translator. LINQKit just simplified writing such expressions, but writing correct queries is completely your task. — Svyatoslav Danyliv, Mar 23 '21 at 12:49
Try to comment out which part is problematic. I vote for `First` in both places. — Svyatoslav Danyliv, Mar 23 '21 at 12:51
I still get the error for not translating the query. After all it is getting super complicated as you say this query is wired enough. I'll mark the answer as accepted, because LinqKit does what it is expected to do. Thanks for the support! — Julian, Mar 23 '21 at 13:03

LinqKit Unable to cast object of type 'System.Linq.Expressions.InstanceMethodCallExpression2' to type 'System.Linq.Expressions.LambdaExpression'

1 Answers1