1

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

Piotr
  • 25
  • 5
  • 1
    EF supports only `Contains` with local collections. But you can use extension method [FilterByItems](https://stackoverflow.com/a/67666993/10646316) - `context.Person.FilterByItems(prefixes, (person, p) => EF.Functions.Like(person.Name, p + "%"), true)` – Svyatoslav Danyliv Feb 21 '23 at 14:05
  • 1
    But in this case better to use `person.Name.StartsWith(p)` – Svyatoslav Danyliv Feb 21 '23 at 14:07
  • Bu person.Name.StartsWith(p) returns error too – Piotr Feb 21 '23 at 14:08
  • 1
    I'll repeat again: **EF supports only Contains with local collections**. Use my extension method which I have created for this frequently asked question. But instead of `Like` use `StartsWith` when using extension `FilterByItems`. – Svyatoslav Danyliv Feb 21 '23 at 14:11

1 Answers1

1

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.

Oliver Schimmer
  • 387
  • 2
  • 14
  • *Unfortunately, EF core is still rather limited at translating Any or All to EXISTS* - that's wrong. EF Core cannot translate these operators when they are applied to local collection. – Svyatoslav Danyliv Feb 21 '23 at 14:38
  • @SvyatoslavDanyliv This is effectively the same thing. Entity Framework 6 under .NET Framework had no issue with that. And there are many other cases than this one, where EF core has problems with these operators. – Oliver Schimmer Feb 21 '23 at 14:43
  • It is not the same things. In SQL it should be `SELECT * FROM (VALUES(...)) WHERE ...`. EF do not generate this construction. – Svyatoslav Danyliv Feb 21 '23 at 14:49
  • I meant you saying that "EF Core cannot translate these operators" here is the same thing as saying it's rather limited. Please don't twist my words around. – Oliver Schimmer Feb 21 '23 at 14:52