LINQ / EF Core cannot use string.Contains in query

Question

In my opinion this should be pretty straight forward: I have a list of strings and I want to return only the db rows, where the column matches all of the strings contained in the list.

so for instance, if my string search query is { "R", "E", "I" } it should return all records that contain the letters R, E and I in the column MyStringColumn (in any order).

Code Example:

var reiks = new List<string> { "R", "E", "I" };
var result = _context.MyTable.Where(x => reiks.All(r => x.MyStringColumn.Contains(r)));

Unfortunately, this returns the following error:

System.InvalidOperationException. The LINQ expression 'DbSet() .Where(c => __reiks_0 .All(r => c.MyStringColumn.Contains(r)))' 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'.

If I rewrite the code like this it works, but this is only a temporary solution, as I cannot guarantee that there will only be 3 strings:

var result = _context.MyTable.Where(x => ((reiks.Count == 0 || x.MyStringColumn.Contains(reiks[0])) && (reiks.Count <= 1 || x.MyStringColumn.Contains(reiks[1])) && (reiks.Count <= 2 || x.MyStringColumn.Contains(reiks[2]))));

What am I doing wrong? I also tried the first code example with Any instead of All, but it didn't work either way.

Answer 1

You can break down the condition like :

var reiks = new List<string> { "R", "E", "I" };
var query = _context.MyTable.AsQueryable();
foreach(var reik in reiks)
{
    query = query.Where(x => x.MyStringColumn.Contains(reik));
}
var result = query.ToList();

Answer 2

Try this:

var result = _context.MyTable.AsEnumerable()
                             .Where(x => reiks.All(r => x.MyStringColumn.Contains(r)));