Server evaluated search for multiple keywords using linq and c#

It seems like a trivial task, yet searching stackoverflow and the internet I have failed to find a good solution.

I'm implementing simple search functionality taking a string as input and extract the keywords. Next step is to apply the keywords into a query. I have tried to code below, but linq throws an error to change from Server-evaluation to Client-Evalutaion using .AsEnumerable

But I want to do the evalutation on the server and I want to do as much in one round trip as possible. I guess the problem in my code is related to the .Any/.Contains pattern, but I hope someone can help.

public class Person
{
    public string Name { get; set; }
    public string Email { get; set; }
}

List<string> keywords = new List<string> { "John", "Doe" };
IEnumerable<Person> persons = await context.PersonDbSet
    .Where(person => keywords.Any(keyword => person.Name.Contains(keyword)) 
        || keywords.Any(keyword => person.Email.Contains(keyword)))
    .ToListAsync();

Solution

It seems like a trivial task, it's not, it's one of the most inefficient queries. That's why almost all autocomplete filters use StartsWith instead of Contains. Or use a dedicated full text search engine like Elastic.

It looks like what you try to do is generate a series of LIKE '%..%' clauses combined by OR. Clauses like this can't take advantage of any indexes so end up scanning the entire table.

While Any in Boolean Algebra. is equivalent to multiple OR expressions, EF Core can't make such a translation. SQL has its own ALL and ANY keywords that refer to all elements in a result set, not all conditions.

To generate the query you want you'll have to either specify the expressions explicitly, or use a library like LINQKit to make the code a bit cleaner. It will still perform a full table scan :

IQueryable<Person> SearchPersons (params string[] keywords)
{
  var predicate = PredicateBuilder.New<Person>();

  foreach (string keyword in keywords)
  {
    string temp = keyword;
    predicate = predicate.Or (p => p.Name.Contains (temp))
                         .Or (p => p.Email.Contains (temp));
  }
  return dataContext.Persons.AsExpandable().Where (predicate);
}

...

var persons=await SearchPersons(keywords).ToListAsync();

Using Full Text Search

To make this query efficient you'd have to use SQL Server's Full Text Search indexes. The CONTAINS operator can be used to search for words in one or more fields, words near another etc. The examples show that the following queries are possible:

SELECT * FROM PERSONS WHERE CONTAINS( (Name,Email), 'John')

and

SELECT * FROM PERSONS WHERE CONTAINS( Name , 'John OR Doe')

So I suppose both can be combined

SELECT * FROM PERSONS WHERE CONTAINS((Name,Email) , 'John OR Doe')

Doing the same in EF Core can be done with EF.Contains and combining all keywords with OR:

IQueryable<Person> SearchPersons (params string[] keywords)
{

  var term=String.Join(" OR ",keywords);

  return dataContext.Where( p => EF.Contains(p.Email,term) 
                              || EF.Contains(p.Name,term));
}

You could also use FromSqlRaw to specify the condition you want exactly:

var query="SELECT * FROM PERSONS WHERE CONTAINS((Name,Email) , @term)";
var term=String.Join(" OR ",keywords);
var query=dataContext.Persons.FromSqlRaw(query,term);