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

[ghost]

trafficstars

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

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).

[Ling-Cao]

I came up with a workaround, hope it could help you if you are also using SQL Server.

In this case, please try changing code as follows:

var searchTermsStr = query.Replace("  ", "");
var searchTerms = context.Database.SqlQuery<string>($"select value from string_split({new SqlParameter(nameof(searchTermsStr), searchTermsStr)}, ' ')");

var searchResult = await context.Items
            .Where(x => searchTerms.Any(term => x.Title.Contains(term)))
            .ToListAsync();

There are two changes:

• Use the table-valued function string_split to build a DB set instead of local collection.
• Use the String.Contains instead of EF.Function.Like. (The separator should be on demand.)

[roji]

Note: simply expanding Any/All to a series of AND/OR would be a form of dynamic querying. Since different numbers of collection items result in different SQLs, the query wouldn't be cachable (much like Contains with a collection) and cause query plan pollution at the database; it would also be incompatible with precompiled queries/NativeAOT.

However, there may be a different, non-dynamic approach, similar to how OPENJSON can be used to make Contains better (link).

[Ling-Cao]

Note: simply expanding Any/All to a series of AND/OR would be a form of dynamic querying. Since different numbers of collection items result in different SQLs, the query wouldn't be cachable (much like Contains with a collection) and cause query plan pollution at the database; it would also be incompatible with precompiled queryies/NativeAOT.

However, there may be a different, non-dynamic approach, similar to how OPENJSON can be used to make Contains better (link).

Thank you, it works for me to use OPENJOSN instead of string_split which I don't need to worry about the delimiter.

[roji]

This should now work out of the box (and efficiently) with queryable primitive collection support (#30426), see this blog post for more details.

[roji]

Duplicate of #30426