Compiling a query results in client side evaluation

[Epic-Santiago]

A have a query that references values in a class. This works as expected when the query is not compiled. When the query is compiled it executes on the client. This can be seen by the lack of a where clause in the generated SQL. This works correctly in EF6.

Steps to reproduce

Run the provided code.

class MyKey
{
    public string CompanyID;
}

private static void Repro(IceContext db)
{
    var key = new MyKey() { CompanyID = "MyValue" };

    // Not compiled it correctly evaluates on the server.
    var result1 = (from row in db.SysUserComp
                    where row.Company == key.CompanyID
                    select row).FirstOrDefault();

    // Compiled it evaluates on the client.
    Expression<Func<IceContext, MyKey, SysUserComp>> expression =
        (ctx, key_ex) =>
        (from row in ctx.SysUserComp
            where row.Company == key_ex.CompanyID
            select row).FirstOrDefault();
    var compiledQuery = EF.CompileQuery(expression);

    var result2 = compiledQuery(db, key);
}

Further technical details

EF Core version: 2.1.1 Database Provider: Microsoft.EntityFrameworkCore.SqlServer Operating system: Windows 10 IDE: Visual Studio 2017 15.8.9

[smitpatel]

When you are using non-compiled version, your expression tree contains key.CompanyID which can be evaluated to string value hence while translating it, it goes to server as parameter. When you use compiled version key_ex is parameter hence key_ex,CompanyID cannot be evaluated same way as before which causes parameter creation of type key_ex. Since type of it is MyKey which cannot be used in SQL, it would do client where. As a work-around you can parameterize compiled query on companyID and pass in key.CompanyID when calling compiled query.

[Epic-Santiago]

Thanks for the quick reply. I've already implemented the workaround that you mention. As with my previous issue, the problem is scale. Our code base is several million lines of code and we are attempting to go from EF6 to EF Core. Given your explanation above, how/why did this work at EF6?

[ajcvickers]

@Epic-Santiago To answer your question about EF6, this doesn't work yet because we haven't implemented support for this yet. Based on normal triage, this is a feature for which there is a reasonable workaround and for which we have not, as yet, seen significant demand, so we're moving it to the backlog for now. Let's follow up through other channels on whether there are other options--I'm about to respond to an email from Raj about this, so he'l presumably he will then follow up with you.

[ajcvickers]

Note from triage: @smitpatel to look into different workarounds, such as a replacement for CompileQuery that will handle this case.

[akozmic]

Thanks for reviewing. I submitted - https://github.com/aspnet/EntityFrameworkCore/issues/14857 which was marked as duplicate of this one. It has full example to show the problem. Just wanted to add a couple comments.

The workaround is fine for queries with small number of input parameters but there's a limit to the number (I think in EF6 it was 16, but I only remember seeing 5 when I was trying it with EF Core). I could be wrong on the max though.

The other issue is that when I go to call query.Invoke I see a long list of unnamed parameters (arg1, arg2, arg3, etc) in intellisense which makes it difficult to know what to pass. For example, in the issue I added, you pass Skip and Take integers, but when calling Invoke you just see 2 integers named "arg1" and "arg2" Being able to pass the parameter as a reference type or value struct that encapsulates all the options with strongly-named parameter would be invaluable to any dev team.

Thanks!

[smitpatel]

@roji - To fix this issue, we need new funcletizer.

[ajcvickers]

@roji @smitpatel What's the status of the funcletizer?

[smitpatel]

No progress AFAIK.

[roji]

IIRC @maumar was to look at the EF6 funcletizer before any action is taken.

I think we should consider punting if at all possible, i.e. unless there's currently a design flaw that would be impossible to fix without breaking changes later (which I think is improbable). But worth looking into of course.

[dazbradbury]

Just a heads up we ran into this whilst on EF Core 3.1.1 as part of our translation to EF Core.

We can work around individual cases, but it's hard to find all potential cases at compile time so we're having to run, get errors, fix, repeat. If you have any progress on if / when this would be fixed in EF Core 3.latest, would be appreciated.

Worth noting we can't upgrade to EF Core 5 due to it not supporting .Net Standard 2.0.

[Mike-E-angelo]

Bummer, just ran into this issue. I am making heavy use of record and other components with deconstructors. It would seem these are a natural transition into decomposing into the necessary parameters required for the compiled query.

FWIW I would be interested in eyeballing a PR for this. The idea would be to take any parameters into compiled query, look for a deconstructor, if found, iterate through the types that can be server-translated and add them as variables for the query. Is it that simple? I'm guessing not. :) But, if I can be pointed in the right direction (along with any pointers) I would be happy to take a look and see if I can make contribution here. 👍

[Mike-E-angelo]

So I took an extensive look into this over the weekend and decided that this is indeed better handled on the user/application side than with EF directly.

The solution was actually pretty easy. Create an expression visitor that finds all the root usages of a provided parameter and replace them with newly introduced parameters.

For example, an expression like this:

Expression<Func<DbContext, Input, string>> expression
                = (context, input) => context.Set<Subject>()
                                             .Where(x => x.Name == input.Name)
                                             .Select(x => x.Name + input.One.Two.Name)
                                             .Single();

Gets replaced with:

(context, input_parameter_0, input_parameter_1) 
    => context.Set().Where(x => (x.Name == input_parameter_0)).Select(x => (x.Name + input_parameter_1)).Single()

The above expression works like a champ with Sql Server. Or at least Sql Server Local DB as defined as a passing test.

That stated, my solution is highly-specific with my framework, but thought I would share it in case someone else runs into this issue as a starting point.

Here are some passing tests of the expression visitor to get you started with following the breadcrumbs, if interested.