CODEWITHSUNDEEP
c#entity-frameworkwildcardsql-like
ConductedClever
ConductedClever

Reputation: 4305

EF core query could not be translated

I am trying to develop some dynamic type of code that needs the following complicated query to run.

IEnumerable<string> searchKeys = search.KeySearch.Split(',').Select(key => key.Trim());
var searchedIds = _dbContext.Keys
    .Where(key => searchKeys.Any(searchKey => EF.Functions.Like(key.Name, searchKey)))

Actually I want to find the rows containing one of the searchKeys inside. For example if I have searchKeys like this:

var searchKeys = ["name_%", "last_x%];

then I want to find the row for example with name name_alex or with name last_xaka.
But ef cores says that it couldn't be translated and suggests either to write a translatable query or do the query in client (which is not appropriate in my problem).

System.InvalidOperationException: The LINQ expression 'DbSet() .Where(k => __searchKeys_0 .Any(searchKey => __Functions_1 .Like( matchExpression: k.Name, pattern: searchKey)))' 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.

So I want to change the query to a translatable one but I don't know that what is the problem and no clue to start fixing it.

Upvotes: 1

Views: 3747

Answers (1)

mu88
mu88

Reputation: 5434

So the following solution should at least work:

var searchKeys = search.KeySearch.Split(',').Select(key => key.Trim());

var queries = new List<IQueryable<Key>>();
foreach (var searchKey in searchKeys)
{
    queries.Add(_dbContext.Keys.Where(k => EF.Functions.Like(k.Name, searchKey)));
}

var finalQuery = queries[0];
for (var i = 1; i < queries.Count; i++)
{
    finalQuery = finalQuery.Union(queries[i]);
}

var filteredKeys = finalQuery.ToList();

I assume the code to be very unefficient because it will result in multiple UNION statements.

A more performant option would be to build an expression tree Expression<Func<Key, bool>> that basically produces the following result: .Where(k => EF.Functions.Like(k.Name, searchKey[0]) || ... || EF.Functions.Like(k.Name, searchKey[5])).

Upvotes: 3

Related Questions