c# - How do you check if a string contains any strings from a list in Entity Framework?

Question

I am trying to search a database to see if a string contains elements of a list of search terms.

var searchTerms = new List<string> { "car", "232" };
var result = context.Data.Where(data => data.Name.Contains(searchTerms) ||
                                        data.Code.Contains(searchTerms));

This would work if searchTerms was a string but I've been trying to get it to work with a list of strings.

Essentially I need SQL which would say

SELECT * FROM Data
WHERE Name LIKE '%car%'
OR Name LIKE '%232%'
OR Code LIKE '%car%'
OR Code LIKE '%232%'

linq where list contains any in list seems to be the closes thing I could find to the situation.

Where(data => searchTerms.Contains(data.Name) || searchTerms.Contains(data.Code) only brings back exact matches to the search terms list.

I have tried searching for multiple keyword search in Entity Framework also and have exhausted that effort. Is there any way to achieve what I am aiming for?

Answer 1

You can try using Any method, I'm not sure whether it's supported but it's worth trying:

var result = context.Data.Where(data => searchTerms.Any(x => data.Name.Contains(x)) ||
                                        searchTerms.Any(x => data.Code.Contains(x));

If this gives you NotSupportedException you can add AsEnumerable before Where to fetch all records and execute the query in memory rather than DB.