Efcore: Query: Expand Any/All on local collection into predicate with AND/OR

[AkshayBanifatsyevich]

When I have a Where clause with a EF.Functions.Like extension method
Then a runtime "System.InvalidOperationException: The LINQ expression could not be translated." exception is thrown.

Steps to reproduce

Given I have the following code:

public async Task<IEnumerable<Item>> SearchItems(string query)
{
            using (var context = new SomeContext())
            {
                var searchTerms = query.Replace("  ", "").Split(" ");
                var searchResult = await context.Items
                    .Where(x => searchTerms.Any(term => EF.Functions.Like(x.Title, $"%{term}%")))
                    .ToListAsync();

                return searchResult;
            }
}

When execution reaches the WHERE clause, then the following runtime error is thrown:

System.InvalidOperationException: The LINQ expression 'Where<Listing>(
    source: DbSet<Listing>, 
    predicate: (l) => Any<string>(
        source: (Unhandled parameter: __searchTerms_0), 
        predicate: (term) => Like(
            _: (Unhandled parameter: __Functions_1), 
            matchExpression: l.Title, 
            pattern: Format(
                format: "%{0}%", 
                arg0: term))))' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

According to accepted answer https://stackoverflow.com/a/56941963/8128257 this seemed to work in EF6. Is there a way to get this to work in EF Core 3.0.1 in one round trip to the server as described in the answer?

Further technical details

EF Core version: 3.0.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework:. .NET Core 3.0
Operating system: Windows 10 Professional
IDE: Visual Studio 2019 16.3.7

[ajcvickers]

@AkshayBanifatsyevich Thanks for filing this; we will get back to you with more details after our next triage meeting.

[ajcvickers]

@smitpatel to show different way to write the query.

[smitpatel]

Under the hood, EF6 expanded into following only
C# var searchTerms = query.Replace(" ", "").Split(" "); var searchResult = await context.Items .Where(x => EF.Functions.Like(x.Title, $"%{searchTerms[0]}%") || EF.Functions.Like(x.Title, $"%{searchTerms[1]}%")) .ToListAsync();
To do it dynamically when varying size of searchTerms, you need to drop down to expression tree construction.

[gdunit]

Can any further guidance be provided for workarounds where there is a dynamic number of search terms? @smitpatel suggested "drop down to expression tree construction", is there any suggestion on best practice to accomplish this?

The project I am working on has a large number of .Any() queries per above, all of which now throw due to client evaluation after upgrading from 2.2.6 to 3.1.2. Typical predicates:
.Where(x => idCollection.Any(xx => x.Id == x))
.Where(x => stringCollection.Any(xx => x.Name.Startswith(x))

[NetMage]

Using LINQKit, you can create an extension method to make translating the Where...Like...Any pattern easier.

// keyFne - extract key from row
// likeTerms - collection where key must be be like one
// dbq.Where(r => searchTerms.Any(s => EF.Functions.Like(keyFne(r), s)))
public static IQueryable<T> WhereLikeAny<T>(this IQueryable<T> dbq, Expression<Func<T,string>> keyFne, IEnumerable<string> likeTerms) {
    Expression<Func<T,bool>> pred = PredicateBuilder.New<T>();
    foreach (var s in likeTerms)
        pred = pred.Or(r => EF.Functions.Like(keyFne.Invoke(r), s));

    return dbq.Where(pred.Expand());
}

However, EF includes a translation for String.Contains to LIKE '%term%' already, so you could just use Contains:

// keyFne - extract key from row
// searchTerms - collection where one must be in key
// dbq.Where(r => searchTerms.Any(s => keyFne(r).Contains(s)))
public static IQueryable<T> WhereContainsAny<T>(this IQueryable<T> dbq, Expression<Func<T,string>> keyFne, IEnumerable<string> searchTerms) {
    Expression<Func<T,bool>> pred = PredicateBuilder.New<T>();
    foreach (var s in searchTerms)
        pred = pred.Or(r => keyFne.Invoke(r).Contains(s));

    return dbq.Where(pred.Expand());
}

If you wanted to avoid LINQKit, you would have to create your own ExpressionVisitor to handle Invoke and Expand. That may be more suited to a StackOverflow question.

[marchy]

What's the priority/milestone target for this?

This is an EXTREMELY common case used throughout our codebase, blocking our migration.
This is a core regression from both EF6 and EFC 2.2 and still just sitting in 'Backlog'.

[smitpatel]

EFC 2.2

Did client evaluation. You can opt-in client evaluation yourself if that is what you want.

[marchy]

@smitpatel Oh GOD no. In-memory is NOT viable in API scenarios and was a severe mistake to enable in the get-go as it gave a very incomplete representation of framework readiness (thus why the team finally changed this behaviour in 3.x).

We are just finally doing our migration from EF6 after EF Core failed miserably in both the EFC 1.x and 2.x timeframes.

We were hoping 3.1 is production-ready but after seeing how even simple queries like this aren't yet working it doesn't yet seem to be the case.....

[marchy]

PS: We would not have to be going through this pain if the team didn't abandon EF6 before EF Core is ready.

Are cross-platform migrations EVER going to be supported for EF6? (link)

(since clearly EF 3.1 is not prod-ready and I am having deep doubts EFC 5 will be either)

[ErikEJ]

We have been using EF Core 2.2 and 3.1 successfully in production for more than a year now, so I think you are generalising...

[marchy]

@ErikEJ not saying you can't.

But we have a not THAT complex system, and when you can't even compare a list of stings in a query, let alone the bigger things such as TPT that are missing, along with the slew of bugs and surprises that have come up along the way (150+ issues fixed in 3.0), and a "brand new query system" that brings its own surprises such as the upcoming query splitting Little Big Detail which causes queries to max out on production-size datasets.

There are always workarounds you can apply and headaches to take on devoid of business value, but that's not what production-grade entails.

We all want it to get there. As of EF Core 3.1 It is frustratingly still behind the value/productivity that life of EF6 entails, and unfortunately for many this means either not adopting or having dual systems set up where we have both EFCore and EF6 where everything that EFCore can do we do, and everything it can't we run through EF6 (a big complexity-add and pain).