0

I've written this query to find orders whose job title is something like "Assistant Manager" or "mit1" or "mit2", etc.

    var searchTerms = new List<string> { "manager", "mit" };

    var report = await _context.Orders
        .Where(x =>
            searchTerms.Any(searchTerm => x.JobTitle.Contains(searchTerm))
        )
        .Select(x => new {
            x.Id,
            x.JobNumber,
            x.JobTitle,
            x.DateOrdered
        })
        .OrderByDescending(x => x.DateOrdered)
        .Take(1000)
        .ToListAsync();

However, this doesn't translate to SQL. I get this error:

The LINQ expression 'DbSet<Order>
    .Where(o => __searchTerms_0
        .Any(searchTerm => o.JobTitle.Contains(searchTerm)))' 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().

I understand the principle and will evaluate this on the client if absolutely necessary. But before I do that, is there a way to write this query in a way that will translate to SQL?

I want a single field to contain (not match exactly but merely contain) any of the list of provided strings.

Christopher
  • 10,409
  • 13
  • 73
  • 97
  • I think you'll have to build up the expression to be like `x => x.JobTitle.Contains("manager") || x.JobTitle.Contains("mit")`. If you're filtering on anything else that can reduce the number of possible rows it might be better to do that filtering on the client as a bunch of "Like"s on the DB isn't going to be particularly performant. – juharr Aug 19 '21 at 16:33
  • I have simplified the query for Stack Overflow. The real-life query does some other filtering to reduce the count considerably. – Christopher Aug 19 '21 at 16:34

0 Answers0