Assuming I have a database table (aTable) with two columns
id : int
name: string
Requirements: I want to retrieve entries where aTable.name is like a list of strings (stringsToSearchFor).
What I am doing: Currently I am using the following approach
var result=new List<aTable>;
foreach (var aString in stringsToSearchFor)
{
var r = Context.Set<aTable>()
.Any(s => s.name.Contains(searchString))
.ToList();
res.AddRange(r);
}
return result.Distinct();
To optimize it I tried to change the code by eliminating the foreach, to be:
return Context.Set<aTable>()
.Any(s => stringsToSearchFor.Contains(s.name))
.Distinct()
.ToList();
However, this didn't provide the same results as the previous statement. I believe the first statement is correct.
My question: How can I search for multiple strings without creating N database queries (like the 2nd approach)?
Alternative solution: use the EF 6.2 Like
:
.Where(x => stringsToSearchFor.Any(s => DbFunctions.Like(x.name, $"%{s}%")))