EF.Function.Like could not be translated when using List of conditions

I am using EF.Core and want to filter by list of strings.

var prefixes = new List<string> { "sth", "sth2", "sth3" }


var query = context.Person
            .Where(prefixes.Any(p => EF.Functions.Like(person.Name, p +"%"))
            .Select ...

return error, that query could not be translated.

I would like to ask, how this problem can be resolved?

I do not want to filter this in memory, after executing query.

Thank you

>Solution :

Unfortunately, EF core is still rather limited at translating Any or All to EXISTS. You could rewrite your query as a chain of OR statements (disjunctions), however this is something rather difficult with Linq.

If including a package is okay, consider LinqKit. It provides a PredicateBuilder, which you can use this way:

var query = context.Person;

var disjunctionPredicate = PredicateBuilder.New<Person>();
foreach (var eachPrefix in prefixes)
{
    // I am using Contains for LIKE, but EF.Functions.Like probably works, too
    disjunctionPredicate = disjunctionPredicate.Or(p => p.Name.Contains(eachPrefix));
}

var result = await query.Where(disjunctionPredicate).ToListAsync();

A theoretical approach would be to create the Or chain functionally:

var query = context.Person;

Func<Person, bool> disjunctionPredicate = _ => false;
foreach (var eachPrefix in prefixes)
{
    var oldPredicate = disjunctionPredicate;
    disjunctionPredicate = p => oldPredicate(p) || p.Name.Contains(eachPrefix);
}

var result = await query.Where(disjunctionPredicate).ToListAsync();

This works nicely with in-memory collections.
The issues you’ll likely run into are, that EF needs Expressions instead of Funcs, and it won’t be able to translate the Invoke operation (oldPredicate(p)). You would have to ‘expand’ your expression tree afterwards, which is a nightmare – this is why LinqKit is so great.

Leave a Reply