InvalidOperationException in where clause when attempting to create projection with Compile/Invoke

Question

I've got a model and I want to build a simplified query model on top of it using projections. Here we have 2 related 'derived' classes.

note - in the 2nd class ES_PROGRAMGUIDEDAYSCHEDULE, there are 2 implementations of of the projection, the uncommented explicit one works, the commented one out fails.

class ESP_CHANNEL
{
    public string? name { get; set; }

    public static Expression<Func<Channel, ESP_CHANNEL>> Projection
    {
        get
        {
            return x => new ESP_CHANNEL
            {
                name = x.LotIdLeafoftreepartNavigation.Leaf
            };
        }
    }
}

class ES_PROGRAMGUIDEDAYSCHEDULE
{
    public ESP_CHANNEL? ds_channel { get; set; }

    public DateTime? ds_date { get; set; }

    public static IQueryable<ES_PROGRAMGUIDEDAYSCHEDULE> Query(ModelContext context)
    {
        return context.Pgprogramguidedayschedules.Select(ES_PROGRAMGUIDEDAYSCHEDULE.Projection);
    }

    private static Expression<Func<Pgprogramguidedayschedule, ES_PROGRAMGUIDEDAYSCHEDULE>> Projection
    {
        get
        {
            return x => new ES_PROGRAMGUIDEDAYSCHEDULE
            {
                ds_date = x.PgdsIdDayscheduleNavigation.DsTxdate,
                // If I explicitly instantiate an ESP_CHANNEL it works
                ds_channel = new ESP_CHANNEL
                {
                    name = x.PgdsIdDayscheduleNavigation.DsIdScheduleNavigation.SchIdChannelNavigation.LotIdLeafoftreepartNavigation.Leaf
                }
                // note THIS seemingly equivalent implementation via compile/invoke fails
                //ds_channel = ESP_CHANNEL.Projection.Compile().Invoke(x.PgdsIdDayscheduleNavigation.DsIdScheduleNavigation.SchIdChannelNavigation)
            };
        }
    }
}

If I then run this query like this.

var x =
    (from schedule in ES_PROGRAMGUIDEDAYSCHEDULE.Query(ds)
        where schedule.ds_channel.name == channel
        && schedule.ds_date == dt
        select schedule).Take(1);

var ys = x.ToArray();

the explicit implementation in ES_PROGRAMGUIDEDAYSCHEDULE.Projection, works

ds_channel = new ESP_CHANNEL
{
    name = x.PgdsIdDayscheduleNavigation.DsIdScheduleNavigation.SchIdChannelNavigation.LotIdLeafoftreepartNavigation.Leaf
}

whilst the seemingly equivalent implemention, where the instantiation of the ESP_CHANNEL is held in a the projection expression fails

ds_channel = ESP_CHANNEL.Projection.Compile().Invoke(x.PgdsIdDayscheduleNavigation.DsIdScheduleNavigation.SchIdChannelNavigation)

fails with

An unhandled exception of type 'System.InvalidOperationException' occurred in Microsoft.EntityFrameworkCore.dll
The LINQ expression 'DbSet<Pgprogramguidedayschedule>()
    .LeftJoin(
        inner: DbSet<Wondayschedule>(), 
        outerKeySelector: p => EF.Property<decimal?>(p, "PgdsIdDayschedule"), 
        innerKeySelector: w => EF.Property<decimal?>(w, "Oid"), 
        resultSelector: (o, i) => new TransparentIdentifier<Pgprogramguidedayschedule, Wondayschedule>(
            Outer = o, 
            Inner = i
        ))
    .LeftJoin(
        inner: DbSet<Psischedule>(), 
        outerKeySelector: p => EF.Property<decimal?>(p.Inner, "DsIdSchedule"), 
        innerKeySelector: p0 => EF.Property<decimal?>(p0, "Oid"), 
        resultSelector: (o, i) => new TransparentIdentifier<TransparentIdentifier<Pgprogramguidedayschedule, Wondayschedule>, Psischedule>(
            Outer = o, 
            Inner = i
        ))
    .LeftJoin(
        inner: DbSet<Channel>(), 
        outerKeySelector: p => EF.Property<decimal?>(p.Inner, "SchIdChannel"), 
        innerKeySelector: c => EF.Property<decimal?>(c, "LotIdLeafoftreepart"), 
        resultSelector: (o, i) => new TransparentIdentifier<TransparentIdentifier<TransparentIdentifier<Pgprogramguidedayschedule, Wondayschedule>, Psischedule>, Channel>(
            Outer = o, 
            Inner = i
        ))
    .Where(p => __Compile_0.Invoke(p.Inner).name == __channel_1 && p.Outer.Outer.Inner.DsTxdate == __dt_2)' 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'.

any ideas?

(the problem appears to be that its the where clause that fails to interpret the invoke correctly).

EDIT 1

note his query DOES execute correctly.

var x =
    (from schedule in ES_PROGRAMGUIDEDAYSCHEDULE.Query(ds)
        //where schedule.ds_channel.name == channel
        //&& schedule.ds_date == dt
        select schedule).Take(1);

so it appears that it is the where clause navigating via ds_channel that is causing the issue.

Svyatoslav Danyliv · Accepted Answer · 2022-05-03T14:10:11.227

0

You can use LINQKit to make this query working. It needs just configuring DbContextOptions:

builder
    .UseSqlServer(connectionString) // or any other provider
    .WithExpressionExpanding();     // enabling LINQKit extension

Then you can inject your projection using LINQKit's Invoke method (but possible your query will be corrected also)

ds_channel = ESP_CHANNEL.Projection.Invoke(x.PgdsIdDayscheduleNavigation.DsIdScheduleNavigation.SchIdChannelNavigation)

Also you may find helpful this answer. It shows how to hide expression magic from end user.

edited May 03 '22 at 14:10

answered May 03 '22 at 12:43

Svyatoslav Danyliv

21,911
3
16
32

ahha....let me have a look at this – MrD at KookerellaLtd May 03 '22 at 13:54
this is oracle...does that matter? – MrD at KookerellaLtd May 03 '22 at 13:54
It does not matter. It will work for any Database Provider. – Svyatoslav Danyliv May 03 '22 at 14:08
ds_channel = LinqKit.ExtensionsCore.Invoke(ESP_CHANNEL.Projection, x.PgdsIdDayscheduleNavigation.DsIdScheduleNavigation.SchIdChannelNavigation) seems to work – MrD at KookerellaLtd May 03 '22 at 14:24
Try also `ESP_CHANNEL.Projection.Compile()(x.PgdsIdDayscheduleNavigation.DsIdScheduleNavigation.SchIdChannelNavigation)`. It should also work. – Svyatoslav Danyliv May 03 '22 at 14:33
System.InvalidOperationException HResult=0x80131509 Message=variable '__Compile_0' of type 'System.Func`2[sakilaConsole.sakila.Channel,Program+ESP_CHANNEL]' referenced from scope '', but it is not defined – MrD at KookerellaLtd May 03 '22 at 16:48
Will check, probably missing case in LINQKit implementation. – Svyatoslav Danyliv May 03 '22 at 17:28
maybe update your answer, just in case someone else ends up here – MrD at KookerellaLtd May 03 '22 at 18:16
My answer should be correct. I mean that I can create PR for LINQKit to fix appropriate issue. – Svyatoslav Danyliv May 03 '22 at 18:24
oh im sorry yes – MrD at KookerellaLtd May 03 '22 at 20:32

InvalidOperationException in where clause when attempting to create projection with Compile/Invoke

EDIT 1

1 Answers1

Linked